Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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 !!!
In oracle the single quote is used to enclose the strings. But if you need to include the single quote in the string itself then you have to use it twice like '' (they are 2 single quotes and not one double quote).
So in your where condition when you specified 'USERS''DATA', oracle is looking for the string USERS'DATA. (look for the embedded single quote in the string)
Your where clause should be:
WHERE default_tablespace NOT IN ('USERS', 'DATA')
Balaji
If it doesn Received on Mon Jun 04 2007 - 10:18:20 CDT