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

stiffness schrieb:
> 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 !!!
>
>
>

Some thoughts...
According De Morgan laws
http://en.wikipedia.org/wiki/De_Morgan%27s_laws zero IS NOT (red OR black) => zero IS NOT red AND zero IS NOT black oracle IS NOT (mssql OR sybase) => oracle IS NOT mssql AND oracle IS NOT sybase

The list of OR's can be in Oracle rewritten as well to an in list: ten IS (positive OR natural OR even) => ten IN (positive,natural,even)

Since Oracle 10g the default tablespace can be specified at the database level.

Best regards

Maxim Received on Sat Jun 02 2007 - 18:49:31 CDT