Thursday, October 25, 2012

Bringing ONLINE a Datafile that is in RECOVER mode because it was OFFLINE


A recent forums thread was about a Datafile that had been taken OFFLINE by the DBA and was in RECOVER mode.LinkLink
When a DBA sees a datafile in 'RECOVER' mode (in DBA_DATAFILES) he shouldn't jump to the conclusion that he needs to RESTORE *and* RECOVER the datafile -- particularly if it does exist !
A RESTORE is required only if the file doesn't exist on disk (accessible to the database) OR is corrupted.

In "normal" circumstances (i.e. when a RESTORE has been issued), a RECOVER needs to roll-forward through all the ArchiveLogs that have been generated since the point in time of the datafile -- i.e. the backup of that datafile.

However, if the Datafile was taken OFFLINE, although Oracle marks it as requiring RECOVERy (as is seeen in DBA_DATA_FILES.STATUS), it doesn't really need all the ArchiveLogs. It only needs those ArchiveLogs that captured the Checkpoint of the Datafile and it's being taken OFFLINE. Subsequent ArchiveLogs (no matter how many they were) are not required. Thus, if the file was taken OFFLINE four days ago, as the DBA, I need only the ArchiveLogs that captured the last set of transactions not checkpointed into the Datafile and the ArchiveLog that captured the issuance of the ALTER DATABASE DATAFILE filename OFFLINE command. I do NOT need 4 days of ArchiveLogs.

Here, I first present one case where I do not have the ArchiveLog that captured the DATAFILE OFFLINE command. As is evident, datafile /usr/tmp/test_offline cannot be RECOVERed and brought ONLINE :

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL> create tablespace test_offline datafile '/usr/tmp/test_offline' size 10M ;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> alter database datafile '/usr/tmp/test_offline' offline;

Database altered.

SQL> select * from dba_data_files where file_name like '/usr/tmp/test%';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/usr/tmp/test_offline
  6 TEST_OFFLINE                                         AVAILABLE
    6
RECOVER


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/*

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 0

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> !ls -l  /oracle_fs/ArchiveLogs/ORT24FS
total 8
-rw-r----- 1 ora10204 dba 4608 Apr 22 22:33 1_5_684196024.dbf

SQL> alter database datafile '/usr/tmp/test_offline' online;
alter database datafile '/usr/tmp/test_offline' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/test_offline'


SQL> recover datafile 6;
ORA-00279: change 649615 generated at 04/22/2009 22:29:06 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_2_684196024.dbf
ORA-00280: change 649615 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database datafile '/usr/tmp/test_offline' online;
alter database datafile '/usr/tmp/test_offline' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/test_offline'


SQL> drop tablespace test_offline including contents and datafiles;

Tablespace dropped.

SQL> 

I needed ArchiveLog 2 to be able to issue the RECOVER command. However, as I had (seemingly inadvertently or because it is very old file) removed that ArchiveLog from disk, I cannot RECOVER the datafile. Note, however, that if I did have a Tape backup of ArchiveLogs 2 and 3 I would have been able to RECOVER that datfile and then bring it ONLINE (without requiring Sequences 4 and 5).


In this next scenario, I delete only the subsequent ArchiveLogs after the first one after the ALTER DATABASE DATAFILE filename OFFLINE command. (We can assume that I either preserved the required ArchiveLogs or restored them from backup).

SQL> create tablespace t_o_2 datafile '/usr/tmp/t_o_2.dbf' size 10M;

Tablespace created.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> alter system switch logfile;

System altered.

SQL> alter database datafile '/usr/tmp/t_o_2.dbf' offline;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 44
-rw-r----- 1 ora10204 dba  4608 Apr 22 22:33 1_5_684196024.dbf
-rw-r----- 1 ora10204 dba 28160 Apr 22 22:36 1_6_684196024.dbf
-rw-r----- 1 ora10204 dba  6656 Apr 22 22:36 1_7_684196024.dbf

SQL> create table t_2 (col_1 number);

Table created.

SQL> insert into t_2 select object_id from dba_objects;

50601 rows created.

SQL> commit;    

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 900
-rw-r----- 1 ora10204 dba   4608 Apr 22 22:33 1_5_684196024.dbf
-rw-r----- 1 ora10204 dba  28160 Apr 22 22:36 1_6_684196024.dbf
-rw-r----- 1 ora10204 dba   6656 Apr 22 22:36 1_7_684196024.dbf
-rw-r----- 1 ora10204 dba 866304 Apr 22 22:38 1_8_684196024.dbf
-rw-r----- 1 ora10204 dba   1536 Apr 22 22:38 1_9_684196024.dbf

SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/1_8_*.dbf

SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/1_9_*.dbf

SQL> alter database datafile '/usr/tmp/t_o_2.dbf' online;
alter database datafile '/usr/tmp/t_o_2.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/t_o_2.dbf'


SQL> recover datafile 6;
ORA-00279: change 649846 generated at 04/22/2009 22:36:10 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf
ORA-00280: change 649846 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 649874 generated at 04/22/2009 22:36:28 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_7_684196024.dbf
ORA-00280: change 649874 for thread 1 is in sequence #7
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> select * from dba_data_files where file_name like '/usr/tmp/t_o%';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/usr/tmp/t_o_2.dbf
    6 T_O_2                                                AVAILABLE
      6
OFFLINE


SQL> alter database datafile '/usr/tmp/t_o_2.dbf' online;

Database altered.

SQL>


Thus, the RECOVER command for /usr/tmp/t_o_2.dbf required only ArchiveLogs 6 and 7. I did NOT need ArchiveLogs 8, 9 and 10 even though they have been generated since after the particular Datafile was taken OFFLINE.
Therefore, although ArchiveLogs 8, 9 and 10 do capture transactions in *other* Datafiles (and, therefore, would be required if I were to RESTORE and/or RCOVER the other Datafiles), I do not need them for this particular Datafile that was "properly and normally" taken OFFLINE.
As further evidence, see these messages from the alert.log file :
Wed Apr 22 22:36:42 2009
alter database datafile '/usr/tmp/t_o_2.dbf' offline
Wed Apr 22 22:36:42 2009
Completed: alter database datafile '/usr/tmp/t_o_2.dbf' offline
Wed Apr 22 22:36:47 2009
Thread 1 cannot allocate new log, sequence 8
Checkpoint not complete
Current log# 1 seq# 7 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Wed Apr 22 22:36:49 2009
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /oracle_fs/Databases/ORT24FS/redo02.dbf
Wed Apr 22 22:38:15 2009
Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
Current log# 2 seq# 8 mem# 0: /oracle_fs/Databases/ORT24FS/redo02.dbf
Wed Apr 22 22:38:16 2009
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Thread 1 cannot allocate new log, sequence 10
Checkpoint not complete
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Wed Apr 22 22:38:19 2009
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 1 seq# 10 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Wed Apr 22 22:39:19 2009
alter database datafile '/usr/tmp/t_o_2.dbf' online
Wed Apr 22 22:39:19 2009
ORA-1113 signalled during: alter database datafile '/usr/tmp/t_o_2.dbf' online...
Wed Apr 22 22:39:25 2009
ALTER DATABASE RECOVER  datafile 6
Media Recovery Start
parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 6  ...
Wed Apr 22 22:39:28 2009
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 22 22:39:28 2009
Media Recovery Log /oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Apr 22 22:39:31 2009
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 22 22:39:31 2009
Media Recovery Log /oracle_fs/ArchiveLogs/ORT24FS/1_7_684196024.dbf
Wed Apr 22 22:39:31 2009
Media Recovery Complete (ORT24FS)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 22 22:40:15 2009
alter database datafile '/usr/tmp/t_o_2.dbf' online
Wed Apr 22 22:40:15 2009
Starting control autobackup
Control autobackup written to DISK device
     handle '/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/c-4163910544-20090422-03'
Completed: alter database datafile '/usr/tmp/t_o_2.dbf' online 



Thus, Oracle needed only ArchiveLogs 6 and 7 even as I had deleted 8 and 9 from disk (and have no backups of 8 and 9).

Friday, October 12, 2012

SQL set operators


SQL set operators


1 Introduction

SQL set operators allows combine results from two or more SELECT statements. At first sight this looks similar to SQL joins although there is big difference. SQL joins tends to combine columns i.e. with each additionally joined table it is possible to select more and more columns. SQL set operators on the other hand combine rows from different queries with strong preconditions - all involved SELECTS must:
  • retrieve the same number of columns and
  • the data types of corresponding columns in each involved SELECT must be compatible (either the same or with possibility implicitly convert to the data types of the first SELECT statement).
Visually the difference can be explained as follows - joins tend to extend breadthways, but set operations in depth.
SQL join example
SQL set operator example
NB 1! All examples are created for Oracle database and written according to Oracle syntax. However it doesn't matter what database management system is used, many of them with (probably) very little modifications or even exactly the same can be used for every other DBMS supporting set operators. Exactly why they work or why not are described for OracleSQL Server and MySQL. If you need to use them for other DBMSes then you should check these examples yourself although I would be very pleased if you'd send me information what examples are not working on what DBMSes. I will include this info here along with your name.

Contents

1 Introduction
2 Set operator types and syntax
   2.1 Common facts to remember
   2.2 Used tables for examples
   2.3 UNION [DISTINCT] and UNION ALL
   2.4 EXCEPT [DISTINCT] and EXCEPT ALL
   2.5 INTERSECT [DISTINCT] and INTERSECT ALL
   2.6 Raising it to higher levels - are two table data equal?
3 Example usage for various DBMSes
   3.1 Oracle
   3.2 Microsoft SQL Server
   3.3 MySQL
   3.4 IBM DB2
4 References and more information

2 Set operator types and syntax

According to SQL Standard there are following Set operator types:
  • UNION [DISTINCT];
  • UNION ALL;
  • EXCEPT [DISTINCT];
  • EXCEPT ALL;
  • INTERSECT [DISTINCT];
  • INTERSECT ALL.
As we can see there are 3 basic types Union, Except and Intersect and all have 2 modifications either Distinct or All. SQL Standard does not enforce keyword Distinct and some DBMSes for example Oracle and SQL Server even do not allow it, therefore if you see just Union, Except or Intersect - these actually mean Union Distinct, Except Distinct and Intersect Distinct.
It is already clear from the very syntax that Distinct modification removes duplicates from the result set, but All modification retains them.
Query syntax is common for all of them:
<query1>      
<SET OPERATOR>
<query1>      
Each query1 and query2 is full-fledged SELECT statements with possible joins, subqueries and other constructions. There is also possibility to combine more than 2 SELECT statements with set operators among them.
Lets look in general overview what the result of each one of them is. Following chart defines 2 queries returning data and various set operator combinations among them.
QueryAQueryBQueryA UNION [DISTINCT] QueryBExample 1QueryA UNION ALL QueryBExample 3QueryA EXCEPT (MINUS) [DISTINCT] QueryB Example 10QueryB EXCEPT (MINUS) [DISTINCT] QueryA Example 11QueryA EXCEPT (MINUS) ALL QueryBExample 14QueryB EXCEPT (MINUS) ALL QueryAExample 15QueryA INTERSECT [DISTINCT] QueryBExample 17QueryA INTERSECT ALL QueryBExample 19
RigaRigaRigaRigaTallinnRigaVilniusRigaRiga
RigaRigaTallinnRigaStockholmTallinnVilniusVilniusRiga
RigaVilniusVilniusRigaTallinnVilniusHelsinkiVilnius
TallinnVilniusHelsinkiRigaTallinnHelsinki
TallinnVilniusStockholmRigaHelsinki
TallinnVilniusTallinnStockholm
VilniusHelsinkiTallinn
HelsinkiTallinn
HelsinkiVilnius
StockholmVilnius
Vilnius
Vilnius
Vilnius
Helsinki
Helsinki
Helsinki
Stockholm
Next chart contains the same QueryA but QueryC returns 0 rows, just to feel some possible quirks.
QueryAQueryCQueryA UNION [DISTINCT] QueryCExample 4QueryA UNION ALL QueryCExample 5QueryA EXCEPT (MINUS) [DISTINCT] QueryCExample 12QueryA EXCEPT (MINUS) ALL QueryCExample 16QueryC EXCEPT (MINUS) [DISTINCT] QueryAQueryC EXCEPT (MINUS) ALL QueryAQueryA INTERSECT [DISTINCT] QueryC Example 18QueryA INTERSECT ALL QueryC
RigaRigaRigaRigaRiga
RigaTallinnRigaTallinnRiga
RigaVilniusRigaVilniusRiga
TallinnHelsinkiTallinnHelsinkiTallinn
TallinnStockholmTallinnStockholmTallinn
TallinnTallinnTallinn
VilniusVilniusVilnius
HelsinkiHelsinkiHelsinki
HelsinkiHelsinkiHelsinki
StockholmStockholmStockholm

2.1 Common facts to remember

There are some facts which probably aren't obvious and should be mentioned. Let's expand requirements for queries to be combined using one of the set operators:
  • column count must be the same;
  • data types of retrieved columns should match or at least should be implicitly convertible by database;
  • one can use many set operators for example Query1 UNION ALL Query2 UNION ALL Query3 MINUS Query4 INTERSECT Query5. In such case one should look into used DB documentation what is the order of operators, because for example Oracle executes operators starting from left to right, but DB2 firstly executes Intersect;
  • Usually returned column names are taken from the first query;
  • Order by clauses for each individual query except the last one cannot be at all (Oracle) or are ignored (MySQL).
Some other facts:
  • UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn't change the final result. See Example 1 andExample 2.
  • EXCEPT operator is NOT commutative, it IS important which query is first, which second using EXCEPT operator. See Example 10 and Example 11.
  • UNION, EXCEPT and INTERSECT used without anything or with DISTINCT returns only unique values. This is especially interesting when one query returning many nonunique rows is UNIONED to another query returning zero rows (Example 4). The final result contains fewer rows than first query.
  • If you know that result sets returned by each query are unique then use UNION ALL, because database doesn't know that and uses more (wasted) resources to filter out duplicates in case of UNION.
  • If you need determined ordering then use Order by clause in the last query. Don't assume that rows from first query will always be returned first.
  • If you need to distinguish which query produced rows then you can add some tag or flag column indicating which query produced them.
  • NULL values using set operators are considered to be equal to each other (Example 9).

2.2 Used tables for examples

Throughout this entire article we will use following tables and table data (the same data as used in tables above):
CREATE TABLE table1 (                       
  id INTEGER NOT NULL PRIMARY KEY,          
  city VARCHAR(10) NOT NULL);               
CREATE TABLE table2 (                       
  id INTEGER NOT NULL PRIMARY KEY,          
  city VARCHAR(10) NOT NULL);               
CREATE TABLE table3 (                       
  city VARCHAR(10) NOT NULL);               
INSERT INTO table1 VALUES (1, 'RIGA');      
INSERT INTO table1 VALUES (2, 'RIGA');      
INSERT INTO table1 VALUES (3, 'RIGA');      
INSERT INTO table1 VALUES (4, 'TALLINN');   
INSERT INTO table1 VALUES (5, 'TALLINN');   
INSERT INTO table1 VALUES (6, 'TALLINN');   
INSERT INTO table1 VALUES (7, 'VILNIUS');   
INSERT INTO table1 VALUES (8, 'HELSINKI');  
INSERT INTO table1 VALUES (9, 'HELSINKI');  
INSERT INTO table1 VALUES (10, 'STOCKHOLM');
INSERT INTO table2 VALUES (1, 'RIGA');      
INSERT INTO table2 VALUES (2, 'RIGA');      
INSERT INTO table2 VALUES (3, 'VILNIUS');   
INSERT INTO table2 VALUES (4, 'VILNIUS');   
INSERT INTO table2 VALUES (5, 'VILNIUS');   
INSERT INTO table2 VALUES (6, 'VILNIUS');   
INSERT INTO table2 VALUES (7, 'HELSINKI');  
COMMIT;                                     

2.3 UNION [DISTINCT] and UNION ALL

These usually are most widely used set operators. Quite many times one cannot get all the result from one Select statement. Then one of the UNIONS can help.
Graphically UNION can be visualised using Venn diagrams. Assume we have two row sets.
Two queries
Then Query1 UNION Query2 would be as follows. Grey area shows resultant set.
SQL Union
Of course the previous picture is very general visualisation and fully real just for sets which contains each element no more than once.
Query1 UNION ALL Query2 would be as follows:
SQL Union all

2.3.1 Examples

As we can see only unique rows are retuned in next example.

Example 1 Unions cities from table1 and table2.

SELECT city FROM table1 
UNION                   
SELECT city FROM table2;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 

Example 2 Unions cities from table2 and table1. The query ordering is not important, result is the same, compare with Example 1.

SELECT city FROM table2 
UNION                   
SELECT city FROM table1;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 
DO NOT ASSUME that Union always return ordered row set. It is NOT TRUE. It is just because of implementation model, i.e. sort is being done to filter out duplicates. At least from version 10 Oracle has possibility to do HASH UNIQUE operation, which doesn't sort rows and you won't get them back sorted. So ALWAYS use Order by clause if you need guaranteed order of rows.
Next example just combines the rows without filtering out duplicates.

Example 3 Unions ALL cities from table1 and table2.

SELECT city FROM table1 
UNION ALL               
SELECT city FROM table2;
                        
CITY                    
----------              
RIGA                    
RIGA                    
RIGA                    
TALLINN                 
TALLINN                 
TALLINN                 
VILNIUS                 
HELSINKI                
HELSINKI                
STOCKHOLM               
RIGA                    
RIGA                    
VILNIUS                 
VILNIUS                 
VILNIUS                 
VILNIUS                 
HELSINKI                
                        
17 rows selected.       

Example 4 UNION [DISTINCT] even with empty set may reduce number of rows. Compare result from first two queries with third query.

SELECT city FROM table1;
                        
CITY                    
----------              
RIGA                    
RIGA                    
RIGA                    
TALLINN                 
TALLINN                 
TALLINN                 
VILNIUS                 
HELSINKI                
HELSINKI                
STOCKHOLM               
                        
10 rows selected.       
                        
SELECT city FROM table3;
                        
no rows selected        
                        
SELECT city FROM table1 
UNION                   
SELECT city FROM table3;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 

Example 5 UNION ALL with empty set gives the same result as without it.

SELECT city FROM table1 
UNION ALL               
SELECT city FROM table3;
                        
CITY                    
----------              
RIGA                    
RIGA                    
RIGA                    
TALLINN                 
TALLINN                 
TALLINN                 
VILNIUS                 
HELSINKI                
HELSINKI                
STOCKHOLM               
                        
10 rows selected.       

Example 6 Each query in Union must return the same number of columns.

SELECT * FROM table1                                         
UNION                                                        
SELECT city FROM table2;                                     
SELECT * FROM table1                                         
*                                                            
ERROR at line 1:                                             
ORA-01789: query block has incorrect number of result columns

Example 7 Of course query can be Unioned to itself. This time all rows are returned because combination of both columns is reviewed.

SELECT * FROM table1 
UNION                
SELECT * FROM table1;
                     
        ID CITY      
---------- ----------
         1 RIGA      
         2 RIGA      
         3 RIGA      
         4 TALLINN   
         5 TALLINN   
         6 TALLINN   
         7 VILNIUS   
         8 HELSINKI  
         9 HELSINKI  
        10 STOCKHOLM 
                     
10 rows selected.    
Along with subquery factoring clause (or common table expression clause, "with" clause) UNION ALL can be used to generate some sample data without having actual tables. It has become very popular in Oracle forums.

Example 8 Using "with" clause to generate sample test data to test inner join functionality.

WITH cities AS (                                                     
  SELECT 1 as cty_id, 'RIGA' as city FROM dual                       
  UNION ALL                                                          
  SELECT 2, 'TALLINN' FROM dual                                      
),                                                                   
streets AS (                                                         
  SELECT 1 as str_id, 1 as str_cty_id, 'BRIVIBAS' as street FROM dual
  UNION ALL                                                          
  SELECT 2, 2, 'NARVA MNT'FROM dual                                  
)                                                                    
SELECT city, street FROM cities                                      
INNER JOIN streets ON (str_cty_id = cty_id);                         
                                                                     
CITY    STREET                                                       
------- ---------                                                    
RIGA    BRIVIBAS                                                     
TALLINN NARVA MNT                                                    
NULL values are considered equal when using with set operators. This is different than usually, for example, testing for eaquality.

Example 9 Using "with" clause to generate two NULL values and unioning them.

WITH null1 AS (              
  SELECT NULL value FROM dual
),                           
null2 AS (                   
  SELECT NULL value FROM dual
)                            
SELECT value FROM null1      
UNION                        
SELECT value FROM null2;     
                             
V                            
-                            
                             
                             
1 row selected.              

2.4 EXCEPT [DISTINCT] and EXCEPT ALL

EXCEPT returns unique rows that are returned by the first query but are NOT returned by the second query. EXCEPT ALL does the same but retains cardinality, for example, if the first query returns two values of X and second only one, then EXCEPT won't return X but EXCEPT ALL would return one instance of X.
Oracle uses MINUS operator instead of EXCEPT, but the functionality is the same. None of the Oracle, SQL Server and MySQL has implemented EXCEPT ALL. It can be simulated using analytic functions as shown in Example 14 till Example 16.
Usually EXCEPT is used to compare date in different data sources (tables) to find differences, for example, differences in the same tables across test and production and/or actual copy and backup.
Visually Query1 EXCEPT Query2 can be expressed as follows:
SQL except (minus) A-B
Obviously diagram is not symmetric therefore for Query2 EXCEPT Query1 we get different picture:
SQL except (minus) B-A

2.4.1 Examples


Example 10 Cities in table1 except (minus) [distinct] cities in table2.

SELECT city FROM table1 
MINUS                   
SELECT city FROM table2;
                        
CITY                    
----------              
STOCKHOLM               
TALLINN                 

Example 11 Cities in table2 except (minus) [distinct] cities in table1. Of course the result is different than in Example 10.

SELECT city FROM table2 
MINUS                   
SELECT city FROM table1;
                        
no rows selected        
As MINUS filters out duplicates then even subtracting empty set may reduce the initial set.

Example 12 Cities in table1 except (minus) [distinct] empty set (cities in table3).

SELECT city FROM table1 
MINUS                   
SELECT city FROM table3;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 
It is not possible in Oracle and SQL Server to use EXCEPT (MINUS) ALL directly.

Example 13 Minus all doesn't exist in Oracle.

SELECT city FROM table1          
MINUS ALL                        
SELECT city FROM table2;         
                                 
MINUS ALL                        
      *                          
ERROR at line 2:                 
ORA-00928: missing SELECT keyword
However using analytic functions and simple minus it is possible. The main idea for MINUS (EXCEPT) ALL is to retain cardinality, i.e. how many instances of each row exists in source sets. Here analytic function row_number() can help. It just increments counter for each row which is the same as previous and restarts if the row values change. Then we can use simple MINUS [DISTINCT], and show only business columns.

Example 14 Faked minus all using row_number() analytic function. Cities in table1 except (minus) all cities in table2.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
RIGA                                                      
STOCKHOLM                                                 
TALLINN                                                   
TALLINN                                                   
TALLINN                                                   

Example 15 Faked minus all using row_number() analytic function. Cities in table2 except (minus) all cities in table1.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
VILNIUS                                                   
VILNIUS                                                   
VILNIUS                                                   
Any set Minus all empty set doesn't change. Just like with Union all.

Example 16 Faked minus all using row_number() analytic function. Cities in table1 except (minus) all empty set (cities in table3).

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table3                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
HELSINKI                                                  
RIGA                                                      
RIGA                                                      
RIGA                                                      
STOCKHOLM                                                 
TALLINN                                                   
TALLINN                                                   
TALLINN                                                   
VILNIUS                                                   
It is obvious that subtracting anything from empty set will always be empty set therefore I won't show you these examples. It is true for both modifications of except (minus) - distinct and all.

2.5 INTERSECT [DISTINCT] and INTERSECT ALL

Intersect returns only these rows, which are in both tables. Intersect [distinct] returns just unique rows, but intersect all retains cardinality. Intersect is commutative, just like union - it is not important which query is the first, which second one.
Picture for Query1 INTERSECT Query2 is as follows:
SQL intersect

2.5.1 Examples


Example 17 Cities in table1 intersect [distinct] cities in table2.

SELECT city FROM table1 
INTERSECT               
SELECT city FROM table2;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
VILNIUS                 

Example 18 Cities in table2 intersect [distinct] empty set (cities in table3). Every intersection with empty set is empty set.

SELECT city FROM table1 
INTERSECT               
SELECT city FROM table3;
                        
no rows selected        
Intersect all is not possible in Oracle or SQL Server just like with Minus (Except) all. But we can use already known workaround.

Example 19 Faked intersect all using row_number() analytic function. Cities in table1 intersect all cities in table2.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  INTERSECT                                               
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
RIGA                                                      
RIGA                                                      
VILNIUS                                                   

2.6 Raising it to higher levels - are two table data equal?

There are times when we need to find whether two table data are equal. And here I mean "really equal" i.e. both the rows are equal and in case of duplicate rows cardinality of them also are the same. So what we need is to test whether the "opposite" of Intersect i.e. rows that are returned only by the first query or the second query is empty set. Visually it would be as in following picture grey area would be empty.
SQL symmetric difference
In set theory "the opposite" of intersect can be referred as Symmetric difference, which is similar to XOR (exclusive OR) in Boolean logic.
Unfortunately there isn't such Symmetric difference operator in SQL. So we need to be more creative. Looking at previous pictures throughout this article it is quite obvious what we need:
(Query 1 MINUS Query2)
UNION                 
(Query 2 MINUS Query1)
In case of absolutely unique rows it would be sufficient - as soon as it returns at least one row, tables' data are not equal. But. We have to remember that there might be duplicates and amount of them might be different in both result sets. So then we'd need:
(Query 1 MINUS ALL Query2)
UNION ALL                 
(Query 2 MINUS ALL Query1)
Let's look at real examples.

Example 20 Distinct Symmetric difference of table1 and table2.

(SELECT city FROM table1 
MINUS                    
SELECT city FROM table2) 
UNION                    
(SELECT city FROM table1 
MINUS                    
SELECT city FROM table2);
                         
CITY                     
----------               
STOCKHOLM                
TALLINN                  
It is obvious that somehow these tables are different. But exactly how? Then we'd need smarter query.

Example 21 Symmetric difference retaining cardinality of table1 and table2.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q                                                       
UNION ALL                                                 
SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
RIGA                                                      
STOCKHOLM                                                 
TALLINN                                                   
TALLINN                                                   
TALLINN                                                   
VILNIUS                                                   
VILNIUS                                                   
VILNIUS                                                   
So these are rows that are left outside in one or another table. What if we'd like to know in what table exactly? Just add a flag column.

Example 22 Symmetric difference retaining cardinality and showing what is missed of table1 and table2.

SELECT city, 2 flag FROM (                                
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q                                                       
UNION ALL                                                 
SELECT city, 1 flag FROM (                                
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
) q;                                                      
                                                          
CITY             FLAG                                     
---------- ----------                                     
HELSINKI            2                                     
RIGA                2                                     
STOCKHOLM           2                                     
TALLINN             2                                     
TALLINN             2                                     
TALLINN             2                                     
VILNIUS             1                                     
VILNIUS             1                                     
VILNIUS             1                                     
So we can see that table2 misses one Helsinki, one Stockholm and 3 Tallin rows and table1 misses 3 Vilnius rows. If we'd add these rows, then they'd contain exactly the same cities with exactly the same cardinality.

Wednesday, August 1, 2012

User password expiry check


set linesize 999
set pagesize 999
col expiry_date for a20
select NAME, username , expiry_date , account_status from v$database, dba_users where expiry_date < sysdate+30 and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' )and username IN ('SYSTEM','SYS','DBSNMP')
order by account_status, expiry_date, username
/

Tuesday, July 10, 2012

RMAN error ORA-00230: operation disallowed: snapshot control file enqueue unavailable

I found an error message in the alertlog about RMAN backup.

Mod Nov 07 08:46:02 NZDT 2011
Starting control autobackup
Autobackup failed with following error
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

The error looks like other process is holding the controlfile hence the rman can’t do the snapshot. Most likely is the previous backup process job is still holding it, so let’s check what we have in the database

SQL> select sid, serial#, status, logon_time from v$session where program like ‘%rman%’;

SID SERIAL# STATUS LOGON_TIME
—– ——- ———— —————
198 67 ACTIVE 06-NOV-11
219 31393 INACTIVE 06-NOV-11
203 10571 INACTIVE 06-NOV-11
210 403 INACTIVE 06-NOV-11
199 3791 INACTIVE 06-NOV-11
200 2627 INACTIVE 06-NOV-11

as we can see there are left running processes over the weekend. Now the question is why the weekend backup process is still running? I checked the filesystem and found I can’t access (hung session) the autofs mount point, this is the reason why the process is still running. The SA restarted the autofs process to resolve the issue

Wednesday, June 27, 2012

Log On Scripts and Prevent Users Login frm another Tools

To limiting access of a user to a database based on time interval. We can prevent a specific user to access to a database between 08 and 22.
created a logon script and see how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kan identified by kan;
 
User created.
 
SQL> grant connect, resource to kan;
 
Grant succeeded.
 
SQL> conn kan/kan
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAN' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kan/kan
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kan/kan
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAN! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>




To prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

Thursday, October 25, 2012

Bringing ONLINE a Datafile that is in RECOVER mode because it was OFFLINE


A recent forums thread was about a Datafile that had been taken OFFLINE by the DBA and was in RECOVER mode.LinkLink
When a DBA sees a datafile in 'RECOVER' mode (in DBA_DATAFILES) he shouldn't jump to the conclusion that he needs to RESTORE *and* RECOVER the datafile -- particularly if it does exist !
A RESTORE is required only if the file doesn't exist on disk (accessible to the database) OR is corrupted.

In "normal" circumstances (i.e. when a RESTORE has been issued), a RECOVER needs to roll-forward through all the ArchiveLogs that have been generated since the point in time of the datafile -- i.e. the backup of that datafile.

However, if the Datafile was taken OFFLINE, although Oracle marks it as requiring RECOVERy (as is seeen in DBA_DATA_FILES.STATUS), it doesn't really need all the ArchiveLogs. It only needs those ArchiveLogs that captured the Checkpoint of the Datafile and it's being taken OFFLINE. Subsequent ArchiveLogs (no matter how many they were) are not required. Thus, if the file was taken OFFLINE four days ago, as the DBA, I need only the ArchiveLogs that captured the last set of transactions not checkpointed into the Datafile and the ArchiveLog that captured the issuance of the ALTER DATABASE DATAFILE filename OFFLINE command. I do NOT need 4 days of ArchiveLogs.

Here, I first present one case where I do not have the ArchiveLog that captured the DATAFILE OFFLINE command. As is evident, datafile /usr/tmp/test_offline cannot be RECOVERed and brought ONLINE :

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL> create tablespace test_offline datafile '/usr/tmp/test_offline' size 10M ;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> alter database datafile '/usr/tmp/test_offline' offline;

Database altered.

SQL> select * from dba_data_files where file_name like '/usr/tmp/test%';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/usr/tmp/test_offline
  6 TEST_OFFLINE                                         AVAILABLE
    6
RECOVER


SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/*

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 0

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> !ls -l  /oracle_fs/ArchiveLogs/ORT24FS
total 8
-rw-r----- 1 ora10204 dba 4608 Apr 22 22:33 1_5_684196024.dbf

SQL> alter database datafile '/usr/tmp/test_offline' online;
alter database datafile '/usr/tmp/test_offline' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/test_offline'


SQL> recover datafile 6;
ORA-00279: change 649615 generated at 04/22/2009 22:29:06 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_2_684196024.dbf
ORA-00280: change 649615 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database datafile '/usr/tmp/test_offline' online;
alter database datafile '/usr/tmp/test_offline' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/test_offline'


SQL> drop tablespace test_offline including contents and datafiles;

Tablespace dropped.

SQL> 

I needed ArchiveLog 2 to be able to issue the RECOVER command. However, as I had (seemingly inadvertently or because it is very old file) removed that ArchiveLog from disk, I cannot RECOVER the datafile. Note, however, that if I did have a Tape backup of ArchiveLogs 2 and 3 I would have been able to RECOVER that datfile and then bring it ONLINE (without requiring Sequences 4 and 5).


In this next scenario, I delete only the subsequent ArchiveLogs after the first one after the ALTER DATABASE DATAFILE filename OFFLINE command. (We can assume that I either preserved the required ArchiveLogs or restored them from backup).

SQL> create tablespace t_o_2 datafile '/usr/tmp/t_o_2.dbf' size 10M;

Tablespace created.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> alter system switch logfile;

System altered.

SQL> alter database datafile '/usr/tmp/t_o_2.dbf' offline;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle_fs/ArchiveLogs/ORT24FS
Oldest online log sequence     6
Next log sequence to archive   8
Current log sequence           8
SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 44
-rw-r----- 1 ora10204 dba  4608 Apr 22 22:33 1_5_684196024.dbf
-rw-r----- 1 ora10204 dba 28160 Apr 22 22:36 1_6_684196024.dbf
-rw-r----- 1 ora10204 dba  6656 Apr 22 22:36 1_7_684196024.dbf

SQL> create table t_2 (col_1 number);

Table created.

SQL> insert into t_2 select object_id from dba_objects;

50601 rows created.

SQL> commit;    

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -l /oracle_fs/ArchiveLogs/ORT24FS
total 900
-rw-r----- 1 ora10204 dba   4608 Apr 22 22:33 1_5_684196024.dbf
-rw-r----- 1 ora10204 dba  28160 Apr 22 22:36 1_6_684196024.dbf
-rw-r----- 1 ora10204 dba   6656 Apr 22 22:36 1_7_684196024.dbf
-rw-r----- 1 ora10204 dba 866304 Apr 22 22:38 1_8_684196024.dbf
-rw-r----- 1 ora10204 dba   1536 Apr 22 22:38 1_9_684196024.dbf

SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/1_8_*.dbf

SQL> !rm /oracle_fs/ArchiveLogs/ORT24FS/1_9_*.dbf

SQL> alter database datafile '/usr/tmp/t_o_2.dbf' online;
alter database datafile '/usr/tmp/t_o_2.dbf' online
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: '/usr/tmp/t_o_2.dbf'


SQL> recover datafile 6;
ORA-00279: change 649846 generated at 04/22/2009 22:36:10 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf
ORA-00280: change 649846 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 649874 generated at 04/22/2009 22:36:28 needed for thread 1
ORA-00289: suggestion : /oracle_fs/ArchiveLogs/ORT24FS/1_7_684196024.dbf
ORA-00280: change 649874 for thread 1 is in sequence #7
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> select * from dba_data_files where file_name like '/usr/tmp/t_o%';

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/usr/tmp/t_o_2.dbf
    6 T_O_2                                                AVAILABLE
      6
OFFLINE


SQL> alter database datafile '/usr/tmp/t_o_2.dbf' online;

Database altered.

SQL>


Thus, the RECOVER command for /usr/tmp/t_o_2.dbf required only ArchiveLogs 6 and 7. I did NOT need ArchiveLogs 8, 9 and 10 even though they have been generated since after the particular Datafile was taken OFFLINE.
Therefore, although ArchiveLogs 8, 9 and 10 do capture transactions in *other* Datafiles (and, therefore, would be required if I were to RESTORE and/or RCOVER the other Datafiles), I do not need them for this particular Datafile that was "properly and normally" taken OFFLINE.
As further evidence, see these messages from the alert.log file :
Wed Apr 22 22:36:42 2009
alter database datafile '/usr/tmp/t_o_2.dbf' offline
Wed Apr 22 22:36:42 2009
Completed: alter database datafile '/usr/tmp/t_o_2.dbf' offline
Wed Apr 22 22:36:47 2009
Thread 1 cannot allocate new log, sequence 8
Checkpoint not complete
Current log# 1 seq# 7 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Wed Apr 22 22:36:49 2009
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /oracle_fs/Databases/ORT24FS/redo02.dbf
Wed Apr 22 22:38:15 2009
Thread 1 cannot allocate new log, sequence 9
Checkpoint not complete
Current log# 2 seq# 8 mem# 0: /oracle_fs/Databases/ORT24FS/redo02.dbf
Wed Apr 22 22:38:16 2009
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Thread 1 cannot allocate new log, sequence 10
Checkpoint not complete
Current log# 3 seq# 9 mem# 0: /oracle_fs/Databases/ORT24FS/redo03.dbf
Wed Apr 22 22:38:19 2009
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 1 seq# 10 mem# 0: /oracle_fs/Databases/ORT24FS/redo01.dbf
Wed Apr 22 22:39:19 2009
alter database datafile '/usr/tmp/t_o_2.dbf' online
Wed Apr 22 22:39:19 2009
ORA-1113 signalled during: alter database datafile '/usr/tmp/t_o_2.dbf' online...
Wed Apr 22 22:39:25 2009
ALTER DATABASE RECOVER  datafile 6
Media Recovery Start
parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 6  ...
Wed Apr 22 22:39:28 2009
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 22 22:39:28 2009
Media Recovery Log /oracle_fs/ArchiveLogs/ORT24FS/1_6_684196024.dbf
ORA-279 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Wed Apr 22 22:39:31 2009
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 22 22:39:31 2009
Media Recovery Log /oracle_fs/ArchiveLogs/ORT24FS/1_7_684196024.dbf
Wed Apr 22 22:39:31 2009
Media Recovery Complete (ORT24FS)
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT
Wed Apr 22 22:40:15 2009
alter database datafile '/usr/tmp/t_o_2.dbf' online
Wed Apr 22 22:40:15 2009
Starting control autobackup
Control autobackup written to DISK device
     handle '/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/c-4163910544-20090422-03'
Completed: alter database datafile '/usr/tmp/t_o_2.dbf' online 



Thus, Oracle needed only ArchiveLogs 6 and 7 even as I had deleted 8 and 9 from disk (and have no backups of 8 and 9).

Friday, October 12, 2012

SQL set operators


SQL set operators


1 Introduction

SQL set operators allows combine results from two or more SELECT statements. At first sight this looks similar to SQL joins although there is big difference. SQL joins tends to combine columns i.e. with each additionally joined table it is possible to select more and more columns. SQL set operators on the other hand combine rows from different queries with strong preconditions - all involved SELECTS must:
  • retrieve the same number of columns and
  • the data types of corresponding columns in each involved SELECT must be compatible (either the same or with possibility implicitly convert to the data types of the first SELECT statement).
Visually the difference can be explained as follows - joins tend to extend breadthways, but set operations in depth.
SQL join example
SQL set operator example
NB 1! All examples are created for Oracle database and written according to Oracle syntax. However it doesn't matter what database management system is used, many of them with (probably) very little modifications or even exactly the same can be used for every other DBMS supporting set operators. Exactly why they work or why not are described for OracleSQL Server and MySQL. If you need to use them for other DBMSes then you should check these examples yourself although I would be very pleased if you'd send me information what examples are not working on what DBMSes. I will include this info here along with your name.

Contents

1 Introduction
2 Set operator types and syntax
   2.1 Common facts to remember
   2.2 Used tables for examples
   2.3 UNION [DISTINCT] and UNION ALL
   2.4 EXCEPT [DISTINCT] and EXCEPT ALL
   2.5 INTERSECT [DISTINCT] and INTERSECT ALL
   2.6 Raising it to higher levels - are two table data equal?
3 Example usage for various DBMSes
   3.1 Oracle
   3.2 Microsoft SQL Server
   3.3 MySQL
   3.4 IBM DB2
4 References and more information

2 Set operator types and syntax

According to SQL Standard there are following Set operator types:
  • UNION [DISTINCT];
  • UNION ALL;
  • EXCEPT [DISTINCT];
  • EXCEPT ALL;
  • INTERSECT [DISTINCT];
  • INTERSECT ALL.
As we can see there are 3 basic types Union, Except and Intersect and all have 2 modifications either Distinct or All. SQL Standard does not enforce keyword Distinct and some DBMSes for example Oracle and SQL Server even do not allow it, therefore if you see just Union, Except or Intersect - these actually mean Union Distinct, Except Distinct and Intersect Distinct.
It is already clear from the very syntax that Distinct modification removes duplicates from the result set, but All modification retains them.
Query syntax is common for all of them:
<query1>      
<SET OPERATOR>
<query1>      
Each query1 and query2 is full-fledged SELECT statements with possible joins, subqueries and other constructions. There is also possibility to combine more than 2 SELECT statements with set operators among them.
Lets look in general overview what the result of each one of them is. Following chart defines 2 queries returning data and various set operator combinations among them.
QueryAQueryBQueryA UNION [DISTINCT] QueryBExample 1QueryA UNION ALL QueryBExample 3QueryA EXCEPT (MINUS) [DISTINCT] QueryB Example 10QueryB EXCEPT (MINUS) [DISTINCT] QueryA Example 11QueryA EXCEPT (MINUS) ALL QueryBExample 14QueryB EXCEPT (MINUS) ALL QueryAExample 15QueryA INTERSECT [DISTINCT] QueryBExample 17QueryA INTERSECT ALL QueryBExample 19
RigaRigaRigaRigaTallinnRigaVilniusRigaRiga
RigaRigaTallinnRigaStockholmTallinnVilniusVilniusRiga
RigaVilniusVilniusRigaTallinnVilniusHelsinkiVilnius
TallinnVilniusHelsinkiRigaTallinnHelsinki
TallinnVilniusStockholmRigaHelsinki
TallinnVilniusTallinnStockholm
VilniusHelsinkiTallinn
HelsinkiTallinn
HelsinkiVilnius
StockholmVilnius
Vilnius
Vilnius
Vilnius
Helsinki
Helsinki
Helsinki
Stockholm
Next chart contains the same QueryA but QueryC returns 0 rows, just to feel some possible quirks.
QueryAQueryCQueryA UNION [DISTINCT] QueryCExample 4QueryA UNION ALL QueryCExample 5QueryA EXCEPT (MINUS) [DISTINCT] QueryCExample 12QueryA EXCEPT (MINUS) ALL QueryCExample 16QueryC EXCEPT (MINUS) [DISTINCT] QueryAQueryC EXCEPT (MINUS) ALL QueryAQueryA INTERSECT [DISTINCT] QueryC Example 18QueryA INTERSECT ALL QueryC
RigaRigaRigaRigaRiga
RigaTallinnRigaTallinnRiga
RigaVilniusRigaVilniusRiga
TallinnHelsinkiTallinnHelsinkiTallinn
TallinnStockholmTallinnStockholmTallinn
TallinnTallinnTallinn
VilniusVilniusVilnius
HelsinkiHelsinkiHelsinki
HelsinkiHelsinkiHelsinki
StockholmStockholmStockholm

2.1 Common facts to remember

There are some facts which probably aren't obvious and should be mentioned. Let's expand requirements for queries to be combined using one of the set operators:
  • column count must be the same;
  • data types of retrieved columns should match or at least should be implicitly convertible by database;
  • one can use many set operators for example Query1 UNION ALL Query2 UNION ALL Query3 MINUS Query4 INTERSECT Query5. In such case one should look into used DB documentation what is the order of operators, because for example Oracle executes operators starting from left to right, but DB2 firstly executes Intersect;
  • Usually returned column names are taken from the first query;
  • Order by clauses for each individual query except the last one cannot be at all (Oracle) or are ignored (MySQL).
Some other facts:
  • UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn't change the final result. See Example 1 andExample 2.
  • EXCEPT operator is NOT commutative, it IS important which query is first, which second using EXCEPT operator. See Example 10 and Example 11.
  • UNION, EXCEPT and INTERSECT used without anything or with DISTINCT returns only unique values. This is especially interesting when one query returning many nonunique rows is UNIONED to another query returning zero rows (Example 4). The final result contains fewer rows than first query.
  • If you know that result sets returned by each query are unique then use UNION ALL, because database doesn't know that and uses more (wasted) resources to filter out duplicates in case of UNION.
  • If you need determined ordering then use Order by clause in the last query. Don't assume that rows from first query will always be returned first.
  • If you need to distinguish which query produced rows then you can add some tag or flag column indicating which query produced them.
  • NULL values using set operators are considered to be equal to each other (Example 9).

2.2 Used tables for examples

Throughout this entire article we will use following tables and table data (the same data as used in tables above):
CREATE TABLE table1 (                       
  id INTEGER NOT NULL PRIMARY KEY,          
  city VARCHAR(10) NOT NULL);               
CREATE TABLE table2 (                       
  id INTEGER NOT NULL PRIMARY KEY,          
  city VARCHAR(10) NOT NULL);               
CREATE TABLE table3 (                       
  city VARCHAR(10) NOT NULL);               
INSERT INTO table1 VALUES (1, 'RIGA');      
INSERT INTO table1 VALUES (2, 'RIGA');      
INSERT INTO table1 VALUES (3, 'RIGA');      
INSERT INTO table1 VALUES (4, 'TALLINN');   
INSERT INTO table1 VALUES (5, 'TALLINN');   
INSERT INTO table1 VALUES (6, 'TALLINN');   
INSERT INTO table1 VALUES (7, 'VILNIUS');   
INSERT INTO table1 VALUES (8, 'HELSINKI');  
INSERT INTO table1 VALUES (9, 'HELSINKI');  
INSERT INTO table1 VALUES (10, 'STOCKHOLM');
INSERT INTO table2 VALUES (1, 'RIGA');      
INSERT INTO table2 VALUES (2, 'RIGA');      
INSERT INTO table2 VALUES (3, 'VILNIUS');   
INSERT INTO table2 VALUES (4, 'VILNIUS');   
INSERT INTO table2 VALUES (5, 'VILNIUS');   
INSERT INTO table2 VALUES (6, 'VILNIUS');   
INSERT INTO table2 VALUES (7, 'HELSINKI');  
COMMIT;                                     

2.3 UNION [DISTINCT] and UNION ALL

These usually are most widely used set operators. Quite many times one cannot get all the result from one Select statement. Then one of the UNIONS can help.
Graphically UNION can be visualised using Venn diagrams. Assume we have two row sets.
Two queries
Then Query1 UNION Query2 would be as follows. Grey area shows resultant set.
SQL Union
Of course the previous picture is very general visualisation and fully real just for sets which contains each element no more than once.
Query1 UNION ALL Query2 would be as follows:
SQL Union all

2.3.1 Examples

As we can see only unique rows are retuned in next example.

Example 1 Unions cities from table1 and table2.

SELECT city FROM table1 
UNION                   
SELECT city FROM table2;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 

Example 2 Unions cities from table2 and table1. The query ordering is not important, result is the same, compare with Example 1.

SELECT city FROM table2 
UNION                   
SELECT city FROM table1;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 
DO NOT ASSUME that Union always return ordered row set. It is NOT TRUE. It is just because of implementation model, i.e. sort is being done to filter out duplicates. At least from version 10 Oracle has possibility to do HASH UNIQUE operation, which doesn't sort rows and you won't get them back sorted. So ALWAYS use Order by clause if you need guaranteed order of rows.
Next example just combines the rows without filtering out duplicates.

Example 3 Unions ALL cities from table1 and table2.

SELECT city FROM table1 
UNION ALL               
SELECT city FROM table2;
                        
CITY                    
----------              
RIGA                    
RIGA                    
RIGA                    
TALLINN                 
TALLINN                 
TALLINN                 
VILNIUS                 
HELSINKI                
HELSINKI                
STOCKHOLM               
RIGA                    
RIGA                    
VILNIUS                 
VILNIUS                 
VILNIUS                 
VILNIUS                 
HELSINKI                
                        
17 rows selected.       

Example 4 UNION [DISTINCT] even with empty set may reduce number of rows. Compare result from first two queries with third query.

SELECT city FROM table1;
                        
CITY                    
----------              
RIGA                    
RIGA                    
RIGA                    
TALLINN                 
TALLINN                 
TALLINN                 
VILNIUS                 
HELSINKI                
HELSINKI                
STOCKHOLM               
                        
10 rows selected.       
                        
SELECT city FROM table3;
                        
no rows selected        
                        
SELECT city FROM table1 
UNION                   
SELECT city FROM table3;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 

Example 5 UNION ALL with empty set gives the same result as without it.

SELECT city FROM table1 
UNION ALL               
SELECT city FROM table3;
                        
CITY                    
----------              
RIGA                    
RIGA                    
RIGA                    
TALLINN                 
TALLINN                 
TALLINN                 
VILNIUS                 
HELSINKI                
HELSINKI                
STOCKHOLM               
                        
10 rows selected.       

Example 6 Each query in Union must return the same number of columns.

SELECT * FROM table1                                         
UNION                                                        
SELECT city FROM table2;                                     
SELECT * FROM table1                                         
*                                                            
ERROR at line 1:                                             
ORA-01789: query block has incorrect number of result columns

Example 7 Of course query can be Unioned to itself. This time all rows are returned because combination of both columns is reviewed.

SELECT * FROM table1 
UNION                
SELECT * FROM table1;
                     
        ID CITY      
---------- ----------
         1 RIGA      
         2 RIGA      
         3 RIGA      
         4 TALLINN   
         5 TALLINN   
         6 TALLINN   
         7 VILNIUS   
         8 HELSINKI  
         9 HELSINKI  
        10 STOCKHOLM 
                     
10 rows selected.    
Along with subquery factoring clause (or common table expression clause, "with" clause) UNION ALL can be used to generate some sample data without having actual tables. It has become very popular in Oracle forums.

Example 8 Using "with" clause to generate sample test data to test inner join functionality.

WITH cities AS (                                                     
  SELECT 1 as cty_id, 'RIGA' as city FROM dual                       
  UNION ALL                                                          
  SELECT 2, 'TALLINN' FROM dual                                      
),                                                                   
streets AS (                                                         
  SELECT 1 as str_id, 1 as str_cty_id, 'BRIVIBAS' as street FROM dual
  UNION ALL                                                          
  SELECT 2, 2, 'NARVA MNT'FROM dual                                  
)                                                                    
SELECT city, street FROM cities                                      
INNER JOIN streets ON (str_cty_id = cty_id);                         
                                                                     
CITY    STREET                                                       
------- ---------                                                    
RIGA    BRIVIBAS                                                     
TALLINN NARVA MNT                                                    
NULL values are considered equal when using with set operators. This is different than usually, for example, testing for eaquality.

Example 9 Using "with" clause to generate two NULL values and unioning them.

WITH null1 AS (              
  SELECT NULL value FROM dual
),                           
null2 AS (                   
  SELECT NULL value FROM dual
)                            
SELECT value FROM null1      
UNION                        
SELECT value FROM null2;     
                             
V                            
-                            
                             
                             
1 row selected.              

2.4 EXCEPT [DISTINCT] and EXCEPT ALL

EXCEPT returns unique rows that are returned by the first query but are NOT returned by the second query. EXCEPT ALL does the same but retains cardinality, for example, if the first query returns two values of X and second only one, then EXCEPT won't return X but EXCEPT ALL would return one instance of X.
Oracle uses MINUS operator instead of EXCEPT, but the functionality is the same. None of the Oracle, SQL Server and MySQL has implemented EXCEPT ALL. It can be simulated using analytic functions as shown in Example 14 till Example 16.
Usually EXCEPT is used to compare date in different data sources (tables) to find differences, for example, differences in the same tables across test and production and/or actual copy and backup.
Visually Query1 EXCEPT Query2 can be expressed as follows:
SQL except (minus) A-B
Obviously diagram is not symmetric therefore for Query2 EXCEPT Query1 we get different picture:
SQL except (minus) B-A

2.4.1 Examples


Example 10 Cities in table1 except (minus) [distinct] cities in table2.

SELECT city FROM table1 
MINUS                   
SELECT city FROM table2;
                        
CITY                    
----------              
STOCKHOLM               
TALLINN                 

Example 11 Cities in table2 except (minus) [distinct] cities in table1. Of course the result is different than in Example 10.

SELECT city FROM table2 
MINUS                   
SELECT city FROM table1;
                        
no rows selected        
As MINUS filters out duplicates then even subtracting empty set may reduce the initial set.

Example 12 Cities in table1 except (minus) [distinct] empty set (cities in table3).

SELECT city FROM table1 
MINUS                   
SELECT city FROM table3;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
STOCKHOLM               
TALLINN                 
VILNIUS                 
It is not possible in Oracle and SQL Server to use EXCEPT (MINUS) ALL directly.

Example 13 Minus all doesn't exist in Oracle.

SELECT city FROM table1          
MINUS ALL                        
SELECT city FROM table2;         
                                 
MINUS ALL                        
      *                          
ERROR at line 2:                 
ORA-00928: missing SELECT keyword
However using analytic functions and simple minus it is possible. The main idea for MINUS (EXCEPT) ALL is to retain cardinality, i.e. how many instances of each row exists in source sets. Here analytic function row_number() can help. It just increments counter for each row which is the same as previous and restarts if the row values change. Then we can use simple MINUS [DISTINCT], and show only business columns.

Example 14 Faked minus all using row_number() analytic function. Cities in table1 except (minus) all cities in table2.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
RIGA                                                      
STOCKHOLM                                                 
TALLINN                                                   
TALLINN                                                   
TALLINN                                                   

Example 15 Faked minus all using row_number() analytic function. Cities in table2 except (minus) all cities in table1.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
VILNIUS                                                   
VILNIUS                                                   
VILNIUS                                                   
Any set Minus all empty set doesn't change. Just like with Union all.

Example 16 Faked minus all using row_number() analytic function. Cities in table1 except (minus) all empty set (cities in table3).

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table3                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
HELSINKI                                                  
RIGA                                                      
RIGA                                                      
RIGA                                                      
STOCKHOLM                                                 
TALLINN                                                   
TALLINN                                                   
TALLINN                                                   
VILNIUS                                                   
It is obvious that subtracting anything from empty set will always be empty set therefore I won't show you these examples. It is true for both modifications of except (minus) - distinct and all.

2.5 INTERSECT [DISTINCT] and INTERSECT ALL

Intersect returns only these rows, which are in both tables. Intersect [distinct] returns just unique rows, but intersect all retains cardinality. Intersect is commutative, just like union - it is not important which query is the first, which second one.
Picture for Query1 INTERSECT Query2 is as follows:
SQL intersect

2.5.1 Examples


Example 17 Cities in table1 intersect [distinct] cities in table2.

SELECT city FROM table1 
INTERSECT               
SELECT city FROM table2;
                        
CITY                    
----------              
HELSINKI                
RIGA                    
VILNIUS                 

Example 18 Cities in table2 intersect [distinct] empty set (cities in table3). Every intersection with empty set is empty set.

SELECT city FROM table1 
INTERSECT               
SELECT city FROM table3;
                        
no rows selected        
Intersect all is not possible in Oracle or SQL Server just like with Minus (Except) all. But we can use already known workaround.

Example 19 Faked intersect all using row_number() analytic function. Cities in table1 intersect all cities in table2.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  INTERSECT                                               
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
RIGA                                                      
RIGA                                                      
VILNIUS                                                   

2.6 Raising it to higher levels - are two table data equal?

There are times when we need to find whether two table data are equal. And here I mean "really equal" i.e. both the rows are equal and in case of duplicate rows cardinality of them also are the same. So what we need is to test whether the "opposite" of Intersect i.e. rows that are returned only by the first query or the second query is empty set. Visually it would be as in following picture grey area would be empty.
SQL symmetric difference
In set theory "the opposite" of intersect can be referred as Symmetric difference, which is similar to XOR (exclusive OR) in Boolean logic.
Unfortunately there isn't such Symmetric difference operator in SQL. So we need to be more creative. Looking at previous pictures throughout this article it is quite obvious what we need:
(Query 1 MINUS Query2)
UNION                 
(Query 2 MINUS Query1)
In case of absolutely unique rows it would be sufficient - as soon as it returns at least one row, tables' data are not equal. But. We have to remember that there might be duplicates and amount of them might be different in both result sets. So then we'd need:
(Query 1 MINUS ALL Query2)
UNION ALL                 
(Query 2 MINUS ALL Query1)
Let's look at real examples.

Example 20 Distinct Symmetric difference of table1 and table2.

(SELECT city FROM table1 
MINUS                    
SELECT city FROM table2) 
UNION                    
(SELECT city FROM table1 
MINUS                    
SELECT city FROM table2);
                         
CITY                     
----------               
STOCKHOLM                
TALLINN                  
It is obvious that somehow these tables are different. But exactly how? Then we'd need smarter query.

Example 21 Symmetric difference retaining cardinality of table1 and table2.

SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q                                                       
UNION ALL                                                 
SELECT city FROM (                                        
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
) q;                                                      
                                                          
CITY                                                      
----------                                                
HELSINKI                                                  
RIGA                                                      
STOCKHOLM                                                 
TALLINN                                                   
TALLINN                                                   
TALLINN                                                   
VILNIUS                                                   
VILNIUS                                                   
VILNIUS                                                   
So these are rows that are left outside in one or another table. What if we'd like to know in what table exactly? Just add a flag column.

Example 22 Symmetric difference retaining cardinality and showing what is missed of table1 and table2.

SELECT city, 2 flag FROM (                                
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
) q                                                       
UNION ALL                                                 
SELECT city, 1 flag FROM (                                
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table2                                             
  MINUS                                                   
  SELECT city,                                            
    row_number() OVER (PARTITION BY city ORDER BY city) rn
  FROM table1                                             
) q;                                                      
                                                          
CITY             FLAG                                     
---------- ----------                                     
HELSINKI            2                                     
RIGA                2                                     
STOCKHOLM           2                                     
TALLINN             2                                     
TALLINN             2                                     
TALLINN             2                                     
VILNIUS             1                                     
VILNIUS             1                                     
VILNIUS             1                                     
So we can see that table2 misses one Helsinki, one Stockholm and 3 Tallin rows and table1 misses 3 Vilnius rows. If we'd add these rows, then they'd contain exactly the same cities with exactly the same cardinality.

Wednesday, August 1, 2012

User password expiry check


set linesize 999
set pagesize 999
col expiry_date for a20
select NAME, username , expiry_date , account_status from v$database, dba_users where expiry_date < sysdate+30 and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' )and username IN ('SYSTEM','SYS','DBSNMP')
order by account_status, expiry_date, username
/

Tuesday, July 10, 2012

RMAN error ORA-00230: operation disallowed: snapshot control file enqueue unavailable

I found an error message in the alertlog about RMAN backup.

Mod Nov 07 08:46:02 NZDT 2011
Starting control autobackup
Autobackup failed with following error
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

The error looks like other process is holding the controlfile hence the rman can’t do the snapshot. Most likely is the previous backup process job is still holding it, so let’s check what we have in the database

SQL> select sid, serial#, status, logon_time from v$session where program like ‘%rman%’;

SID SERIAL# STATUS LOGON_TIME
—– ——- ———— —————
198 67 ACTIVE 06-NOV-11
219 31393 INACTIVE 06-NOV-11
203 10571 INACTIVE 06-NOV-11
210 403 INACTIVE 06-NOV-11
199 3791 INACTIVE 06-NOV-11
200 2627 INACTIVE 06-NOV-11

as we can see there are left running processes over the weekend. Now the question is why the weekend backup process is still running? I checked the filesystem and found I can’t access (hung session) the autofs mount point, this is the reason why the process is still running. The SA restarted the autofs process to resolve the issue

Wednesday, June 27, 2012

Log On Scripts and Prevent Users Login frm another Tools

To limiting access of a user to a database based on time interval. We can prevent a specific user to access to a database between 08 and 22.
created a logon script and see how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kan identified by kan;
 
User created.
 
SQL> grant connect, resource to kan;
 
Grant succeeded.
 
SQL> conn kan/kan
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAN' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kan/kan
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kan/kan
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAN! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>




To prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

My Blog List