Re: (HELP !!! ) NEED TO WRITE A SCRIPT SHOWING ONLY EXCEPTIONS

On Jun 2, 6:41 pm, "stiffness" <vze2t..._at_verizon.net> wrote:
> Hello,
>
> I am querying the data dictionary for anyone with a improperly defined
> default tablespace. The default table space for DBA_USER should be either
> USERS or DATA*. I need my script to SHOW ONLY THE EXCEPTIONS meaning those
> individuals who have improperly defined table spaces that are not by either
> of the above. HERE IS WHAT I HAVE ATTEMPTED.
>
> Attempt 1.
>
> SQL> select username, default_tablespace, created
> 2 FROM dba_users
> 3 WHERE default_tablespace<>'USERS''DATA';
>
> The above script worked BUT in the reverse showing users in USERS AND DATA
> default_tablespace.
>
> Attempt 2.
>
> SQL> select username, default_tablespace, created
> 2 FROM dba_users
> 3 WHERE NOT default_tablespace='USERS'
> 4 OR NOT default_tablespace='DATA';
>
> The above script also worked but in the reverse. HELP !!!

It looks like a Boolean logic problem. The good records (the ones that you do not want):
WHERE
  DEFAULT_TABLESPACE IN ('USERS','DATA') (or)

WHERE
  DEFAULT_TABLESPACE='USERS' OR DEFAULT_TABLESPACE='DATA' For the bad records (the ones that you want), you must negate the above:
WHERE
  DEFAULT_TABLESPACE NOT IN ('USERS','DATA') (or)

WHERE
  DEFAULT_TABLESPACE<>'USERS' AND DEFAULT_TABLESPACE<>'DATA'

The last example may be a bit hard to follow, in Boolean logic: NOT ( USERS or DATA ) = NOT(USERS) and NOT(DATA)

One final method, if the Boolean logic is difficult to decode for other problems:
SELECT
  USERNAME,
  DEFAULT_TABLESPACE,
  CREATED
FROM
  DBA_USERS
MINUS
SELECT
  USERNAME,
  DEFAULT_TABLESPACE,
  CREATED
FROM
  DBA_USERS
WHERE
  DEFAULT_TABLESPACE IN ('USERS','DATA'); Final note: NULL values in other situations must be anticipated and handled.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jun 02 2007 - 20:36:20 CDT