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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: create table issue

Re: create table issue

From: Jared Still <jkstill_at_gmail.com>
Date: Tue, 28 Jun 2005 11:02:48 -0700
Message-ID: <bf463805062811024eedb757@mail.gmail.com>


Nice idea Peter.

Try running the following against a few databases.

The proliferatino of reserved words makes it very likely there will be several violations.

Try commenting out the user exclusion lines and see how many Oracle schemas violate reserved word usage.

Jared

col owner format a15
col object_type format a15 head 'OBJECT TYPE' col column_name format a30 head 'COLUMN NAME' col name format a30 head 'TABLE/VIEW/PROC NAME'

set linesize 100
set pagesize 60

break on owner on name

select n.owner, n.name <http://n.name>, n.column_name, n.object_type from
(
select owner, object_name name, null column_name, object_type from dba_objects o, v$reserved_words w
where o.object_name = w.keyword
and o.owner not in

('SYS','WMSYS','XDB','SYSTEM','MDSYS','PUBLIC','PERFSTAT','OUTLN','CTXSYS')
and o.owner not like 'OEM\_%' escape '\'
and o.owner not like 'FLOWS\_%' escape '\'
union
select o.owner, o.table_name name, o.column_name, 'COLUMN' object_type from dba_tab_columns o, v$reserved_words w where o.column_name = w.keyword
and o.owner not in
('SYS','WMSYS','XDB','SYSTEM','MDSYS','PUBLIC','PERFSTAT','OUTLN','CTXSYS')
and o.owner not like 'OEM\_%' escape '\'
and o.owner not like 'FLOWS\_%' escape '\'
) n
order by owner, name, object_type, column_name /

On 6/28/05, Peter.Hitchman_at_thomson.com <Peter.Hitchman_at_thomson.com> wrote:
>
> Hi,
> There is a view v$reserved_words that lists all of the reserved words. So
> you could use that to find tables/indexes etc created by surrounding the
> identifier in double quotes.
> So I just tried it:-
> create table "InserT" ( dummy char(1));
> select owner, table_name
> from dba_tables d, v$reserved_words w
> where upper(keyword) = upper(table_name)
> ---> OWNER TABLE_NAME
> --------------- -----------------------------------
> OPS$PHITCHMA InserT
> 1 row selected.
> Regards
> Pete
>
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]*On Behalf Of *Onkar N Tiwary
> *Sent:* 28 June 2005 14:16
> *To:* Giovanni Cuccu
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: create table issue
>
> hi Giovanni,
> Thanx for the reply. But u know my main doubt is how to find such object
> created using quotes. It must be recognised differently by oracle shand
> there ould be entry for such object. If I am not wrong. I am trying to find
> that method where in we can find such objects created using quotes.
>
> On 6/28/05, Giovanni Cuccu <giovanni.cuccu_at_gmail.com> wrote:
> >
> > Hi,
> > I'm not a guru so my answer may be incorrect but I believe that
> > Oracle implicitly use the uppercase name of your item (table, but also
> > column, etc) when try to reference it. If you want to give an item a
> > "particular" name (as oracle 10g does with tables in the recyclebin)
> > you must enclose it with duble quotes; this indicates that the name
> > should be taken as you typed it. You can find all the names (in the
> > form required by Oracle for checking their existence) in the
> > corresponding data_dictionary view (dba_tables for examples).
> > Hope this helps,
> > Giovanni
> >
> >
> > On 6/28/05, Onkar N Tiwary <onkarnath.tiwary_at_gmail.com> wrote:
> > > Hi gurus,
> > >
> > > Normally oracle says we can not create tables using the keywords but
> > when
> > > one issues CREATE TABLE "INSERT" (col datatype);", it gets created. It
> > can
> > > always be referenced and other tasks can be done easily on the table
> > but the
> > > table name must always be in double quotes. My question is how oracle
> > > identifies these tables and where does makes the entry of such tables
> > for
> > > regonising? If I don know about this double quotes and the table has
> > been
> > > created using double quotes then how to find such objects and from
> > > where?????
> > >
> > >
> > > --
> > > Thanks & Regards,
> > > T. Onkar Nath
> > > Ph : +91-9826728111(Cell)
> > > to_onkar_at_yahoo.com
> > > onkarnath.tiwary_at_gmail.com
> > >
> >
> >
> > --
> > --------------------------------------------------------------------
> > Another free oracle resource profiler
> > http://sourceforge.net/projects/oraresprof/
> > New version 0.8 with many bug fixes
> > and warnings on file truncated, timed_statistics=false, incorrect
> > tracing deactivation and more
> >
>
>
>
> --
> Thanks & Regards,
> T. Onkar Nath
> Ph : +91-9826728111(Cell)
> to_onkar_at_yahoo.com
> onkarnath.tiwary_at_gmail.com
>
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 28 2005 - 14:08:15 CDT

Original text of this message

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