Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:

 

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> create role role_a

  2  /

 

Role created.

 

SQL> create role role_b

  2  /

 

Role created.

 

SQL> grant role_a to user_1

  2  identified by user_1

  3  /

 

Grant succeeded.

 

SQL> grant role_a, role_b to user_2

  2  identified by user_2

  3  /

 

Grant succeeded.

 

SQL> grant role_b to user_3

  2  identified by user_3

  3  /

 

Grant succeeded.

 

SQL> select distinct grantee from dba_role_privs

  2  where grantee in

  3  (select grantee from dba_role_privs

  4   where granted_role = 'ROLE_A')

  5  and grantee in

  6  (select grantee from dba_role_privs

  7   where granted_role = 'ROLE_B')

  8  /

 

GRANTEE

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

ORACLE

USER_2

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'ROLE_A'

  3  intersect

  4  select grantee from dba_role_privs

  5  where granted_role = 'ROLE_B'

  6  /

 

GRANTEE

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

ORACLE

USER_2

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'BLAH'

  3  /

 

GRANTEE

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

ORACLE

 

SQL>

ORA-25153

If you try to sort anything other than a small amount of data, and the sort has to use disk space, you may see the following error message:

 

SQL> select a.table_name, b.table_name

  2  from dba_tables a, dba_tables b

  3  order by 1;

from dba_tables a, dba_tables b

     *

ERROR at line 2:

ORA-25153: Temporary Tablespace is Empty

 

SQL>

 

This will happen if you:

 

(1)    Copy the datafiles from a source to a target database.

(2)    Recreate the target database’s control file.

(3)    Forget to add a file to its temporary tablespace.

 

You can diagnose this fault as follows. First you have to find the name(s) of the target database’s temporary tablespace(s). In this example there is only one:

 

SQL> select distinct temporary_tablespace

  2  from dba_users

  3  /

 

TEMPORARY_TABLESPACE

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

TEMP

 

SQL>

 

Then you have to see if it contains any temp files:

 

SQL> select file_name from dba_temp_files

  2  where tablespace_name = 'TEMP'

  3  /

 

no rows selected

 

SQL>

 

If it doesn’t, you have to add one. On this occasion, there was still an old tempfile so I was able to reuse it:

 

  1  alter tablespace temp add tempfile

  2  'test10/andrew/temp_files/temp01.dbf'

  3* reuse

SQL> /

 

Tablespace altered.

 

SQL>

 

Then the sort should work:

 

SQL> col table_name format a25

SQL> l

  1  select a.table_name, b.table_name

  2  from dba_tables a, dba_tables b

  3* order by 1

SQL> /

 

TABLE_NAME                TABLE_NAME

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

ACCESS$                   AQ$_ALERT_QT_H

ACCESS$                   WRI$_DBU_FEATURE_USAGE

ACCESS$                   WRI$_ALERT_HISTORY

ACCESS$                   AQ$_ALERT_QT_T

ACCESS$                   WRH$_FILESTATXS_BL

ACCESS$                   AQ$_ALERT_QT_G

ACCESS$                   AQ$_ALERT_QT_I

ACCESS$                   WRH$_FILESTATXS

ACCESS$                   WRH$_WAITSTAT

ACCESS$                   WRH$_TEMPSTATXS

ACCESS$                   WRH$_SQLSTAT

Etc

Intersect

I read about this in Oracle Database 10g The Complete Reference by Kevin Loney but I tested it on an Oracle 9 database. If you join two or more select statements by intersect, only the rows  which appear in the output from every one of those statements will be displayed. The example below demonstrates this. First create a table of boys' names:

SQL> create table boys_names

  2  (boys_name varchar2(10))

  3  /
  

Table created.

SQL>
  

And also a table of girls' names:
  

SQL> create table girls_names

  2  (girls_name varchar2(10))

  3  /

Table created.

SQL> 

Add some data to the boys_names table then duplicate it:

SQL> insert into boys_names values('Tom');

1 row created.

SQL> insert into boys_names values('Dick');

1 row created.

SQL> insert into boys_names values('Harry');

1 row created.

SQL> insert into boys_names values('Kim');

1 row created.

SQL> insert into boys_names values('Alex');

1 row created.

SQL> insert into boys_names values('Sam');

1 row created.

SQL> insert into boys_names select * from boys_names;

6 rows created.

SQL> 

Do the same with the girls_names table:

SQL> insert into girls_names values('Sarah');

1 row created.

SQL> insert into girls_names values('Tina');

1 row created.

SQL> insert into girls_names values('Brenda');

1 row created.

SQL> insert into girls_names values('Kim');

1 row created.

SQL> insert into girls_names values('Alex');

1 row created.

SQL> insert into girls_names values('Sam');

1 row created.

SQL> insert into girls_names select * from girls_names;

6 rows created.

SQL> 

Display the data in both tables:

SQL> select * from boys_names

  2  /

BOYS_NAME

----------

Tom

Dick

Harry

Kim

Alex

Sam

Tom

Dick

Harry

Kim

Alex

Sam

12 rows selected.

SQL> select * from girls_names

  2  /

GIRLS_NAME

----------

Sarah

Tina

Brenda

Kim

Alex

Sam

Sarah

Tina

Brenda

Kim

Alex

Sam

12 rows selected.

SQL>

Note that 3 names appear twice in both tables. Use intersect to display these rows. Although each of these names appears twice in both tables, they are each displayed only once:

SQL> select * from boys_names

  2  intersect

  3  select * from girls_names

  4  /

BOYS_NAME

----------

Alex

Kim

Sam

SQL>

This could also be achieved as follows:

SQL> select distinct boys_name

  2  from boys_names, girls_names

  3  where boys_name = girls_name

  4  /

BOYS_NAME

----------

Alex

Kim

Sam
  

SQL>