Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: (HELP !!! ) NEED TO WRITE A SCRIPT SHOWING ONLY EXCEPTIONS

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

From: stiffness <vze2t7nz_at_verizon.net>
Date: Sun, 03 Jun 2007 23:40:12 GMT
Message-ID: <wjI8i.772$Vf7.460@trnddc03>


Thanks Charles this was very helpful
"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1180834580.501006.49570_at_k79g2000hse.googlegroups.com...
> 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 Sun Jun 03 2007 - 18:40:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US