Relational Division

Relational division normally requires two not exists sub queries used in three different tables. Below sql snippet shows how division could be employed when all the data is in a single table.

 PROJ  SUBJ
----- -----
    1     1
    1     2
    1     3
    2     3
    2     1
    3     1
    3     2
    3     3
    4     1
    5     2
    6     3                    

Hypothetical situation is that, there are several projects and each project uses number of different subjects. Idea is to find projects that are uses all the subjects.

The theory


theory implementation

select distinct proj from x where proj not in 
(select distinct proj from (select * from 
(select distinct proj from x) t1, 
(select distinct subj from x)  t2) t3
where (proj,subj) not in (select proj,subj from x));

Using With clause in Oracle

with t1 as (select DISTINCT proj from x),
t2 as (select distinct subj from x)
select distinct proj from x where proj not in 
( select proj from t1,t2 where (proj,subj) not in (select * from x));

Using not exists sub queries

select * from (select distinct proj from x) projects
where not exists(
  select * from (select distinct subj from x) subject
  where not exists
    (select * from x
      where x.proj = projects.proj
      and x.subj = subject.subj
      ));

Upgrading 10g to 11g R 2 with DBUA

Another post on upgrading from 10.2.0.4 to 11.2.0.3.

1. Install Oracle 11gR2 software only (enterprise or standard to match the source db. This blog is on standard version. More on metalink note 870814.1).
2. In order to downgrade Oracle Enterprise Manager Database Control after upgrading to the new Oracle Database 11g release save Database Control files and data before upgrade. The emdwgrd utility resides in the ORACLE_HOME/bin directory in the new Oracle
Database 11g release.
3. Follow these steps to save your Database Control files and data:

1. Install the new Oracle Database 11g release.
This step is not required for an inplace patchset upgrade.
2. Set ORACLE_HOME to your old Oracle home.
This step is not required for an inplace patchset upgrade.
3. Set ORACLE_SID to the SID of the database being upgraded.
4. Set PATH, LD_LIBRARY_PATH, and SHLIB_PATH to point to the Oracle home
from which the database is being upgraded.
5. Go to the Oracle home of the new Oracle Database 11g release.
6. Execute one of the following:
if Single DB
emdwgrd -save -sid old_SID -path save_directory
for RAC
setenv EM_REMCP /usr/bin/scp
emdwgrd -save -cluster -sid old_SID -path save_directory
If the release 10g Oracle home is on a shared device, add -shared to the
previous command line.

create a TNS name entry in the 10g Oracle Home (network/admin) with the same name as the SID before running the above command

[oracle@server110 std]$ emdwgrd -save -sid std1 -path /home/oracle/emdu
Enter sys password for database std1?

Mon Sep 14 19:14:24 2009 - Verify EM DB Control files ... pass
Mon Sep 14 19:14:24 2009 - Validating DB Connection to std1 ... pass
ENV var EM_REMCP not defined, check if rcp or scp is configured.
RCP = /usr/bin/rcp -rp, REMSH = /usr/bin/rsh
shared = 0
Mon Sep 14 19:14:28 2009 - Creating directory ... created
Mon Sep 14 19:14:29 2009 - Stopping DB Control ... stopped
Mon Sep 14 19:14:34 2009 - Saving DB Control files
... saved
Mon Sep 14 19:14:50 2009 - Recompiling invalid objects ... recompiled
Mon Sep 14 19:14:57 2009 - Exporting sysman schema for std1 ... exported
Mon Sep 14 19:15:56 2009 - DB Control was saved successfully.
Mon Sep 14 19:15:56 2009 - Starting DB Control ... started
Mon Sep 14 19:17:33 2009 - Dump directory was dropped successfully.

7. Copy the Pre-Upgrade Information Tool (utlu112i.sql) from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home.
8. run above script and examin the output

SQL> SPOOL upgrade_info.log
SQL> @utlu112i.sql
SQL> SPOOL OFF

9. Gather statistics on schemas instructed by utlu111i.sql

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’user_name’);

10. Grant privileges revoked from public to resolve invalid objects
11. If the timezone warning is

Database is using a timezone file older than version 11.

then check the timezone with

select * from v$timezone_file;

if it is not 11 then to fix the problem use the DBMS_DST PL/SQL package and follow the instructions in "Steps to Upgrade Time Zone File and Timestamp with Time Zone Data" in Oracle Database Globalization Support Guide.

if the timezone warning is

Database is using a timezone file greater than version 11.

then before upgrading the database, patch the 11gR2 $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the same version as the one used in the source release database.

12. Create a listener for the new 11g R2 Oracle home
13. Run DBUA from new 11g Home





















14. set the compatible parameter.

alter system set compatible='11.2.0.0.0' scope=spfile ;

15. To upgrade the timezone

shutdown the database and start in upgrade mode

startup upgrade;
exec DBMS_DST.BEGIN_UPGRADE(11);

After PL/SQL executes start the database in normal mode and truncate timezone trigger tables (refer 1585343.1 for automating time-zone upgrade)

truncate table sys.dst$error_table;
truncate table sys.dst$trigger_table;

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel                  => TRUE,
log_errors                => TRUE,
log_errors_table          => 'SYS.DST$ERROR_TABLE',
log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time     => TRUE,
error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

after finishes

BEGIN
DBMS_DST.END_UPGRADE(:numfail);
END;
/