Users, tablespaces, missing tables. Arg.... 3

[Welshbird]

sorry for a not very descriptive title - I guess I'm not really sure how to describe my problem.

I've just installed an XP Pro PC with Oracle 10g as an experiment, and to give others in my office a chance to start using it.

Now, it seems to have all installed ok; I have created a user (me!) and I can connect from SQL*Plus on my machine to the new Oracle box.

I've set myself up with DBA rights at the moment, and I've used USERS as my default table space, and TEMP as my temporary space.

All seems ok so far. I've created my first table, and then tried to look at it.

this is what I see in my SQL*Plus window:

SQL> select table_name from user_tables
  2  /

TABLE_NAME
------------------------------
NewProdMatch
ProdMatch

Elapsed: 00:01:11.35
SQL> desc ProdMatch
ERROR:
ORA-04043: object ProdMatch does not exist


SQL>

So what on earth have I done/not done?
I can 'see' my table, but apparently its not there....

any help would be really appreciated. Thansk guys!


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[KenCunningham]

Hi Fee. Can you try a select owner, table_name from dba_tables where table_name = 'ProdMatch'

to check the ownership of the table in question?

 

[Welshbird]

Its says

SQL> select owner, table_name from dba_tables where table_name = 'ProdMatch'
  2  /

OWNER                          TABLE_NAME
------------------------------ ------------------------------
PHIL                           ProdMatch

and I am logged in as PHIL....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[KenCunningham]

Like you, I would assume that DBA rights would include all the necessary grants, but perhaps PHIL needs 'select any table' privileges?

 

[Welshbird]

I'll have a go and let you know. I'm sure there is just something REALLY obvious I just don;t know about.

Oracle - its all smoke and mirrors! Arg....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[stefanhei]

You created the table casesensitive. Now you have to access it the same way:

desc "ProdMatch"

should work. All objects (fieldnames too...) that were created casesensitive have to be put in double quotes when you reference them.

Stefan

 

[Welshbird]

Fantastic!

Um... how did I create a table casesensetive? I wish I hadn't...

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[KenCunningham]

Interesting....wasn't aware of that. DBA_TAB_PRIVS will give you details of privileges for future reference. I agree about the smoke and mirrors, though!

 

[KenCunningham]

Not so much 'how' more 'why'. Can't you drop the tables and start again?

 

[Welshbird]

I have.... THANK YOU SOO MUCH GUYS!

xxx

Starts all around.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[stefanhei]

You wrote something like

CREATE TABLE "ProdMatch" ("Field1" number,...);

or used a tool that created it that way.

Just do not put names in double quotes when you create an object and you can access it's name case-insensitive (data stored in the table still is case sensitive of course).

Stefan

 

[Welshbird]

PIGGIN' USERS WITH PIGGIN' GIU TOOLS....

La.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[KenCunningham]

GIU? Golfing Union Of Ireland (apparently) ;-)

 

[Welshbird]

Oh yeah.... GUI.... ha.

Slapped 'im now, 'ee won't do it again.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Gents,

You needn't DROP and re-CREATE the tables with the case-sentitivity issue...just use:

RENAME "ProdMatch" to PRODMATCH;

If you have an entire schema-full of tables that need renaming, then you can run a script to compose all of the RENAMEs:

set echo off
set trimspool off
set pagesize 0
set feedback off
spool temp.sql
select 'RENAME "'||table_name||'" to '||table_name||';'
from user_tables
/
spool off
prompt
prompt "Wrote script 'temp.sql'. Execute by, '@temp'."
prompt

RENAME "ProdMatch" to ProdMatch;
RENAME "Yada" to Yada;

Since the target names (ProdMatch and Yada) are not within double quotes, they will end up with the (proper, default) upper casing in the data dictionary.

Let us know your thoughts about this option.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Welshbird]

Excellent idea (as always, from you)

Have a star too.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]