Re: tablespace question.

Song Zhang wrote:

> This is a newbie of oracle dba.
>
> I created a table say cde by
> create table cde (a integer primary key);
> insert into cde values(9);
>
> and create a user with priviledge of connect to the oracle system
> and select from cde table.
>
> create user abc identified by abc
> default tablespace system;
> grant connect to abc;
> grant select on cde to abc;
>
> Then I log on sqlplus and got the following error.
> select * from cde;
> ORA-00942: table or view does not exist
>
> Thanks for the help.
>
> _________________________________________________________________
> MSN 8 with e-mail virus protection service: 2 months FREE*
> http://join.msn.com/?page=features/virus

You have developed a number of bad habits and I would urge you to break them immediately.

[Quoted]
1. Do not use the syntax you used to create a primary key but rather use [Quoted] the ALTER TABLE statement to explicity create your primary key and other constraints. The advantage it gives is that you can specify the tablespace in which the associated index is created, name the constraint with a name corresponding to its type and table, and the same for the index. The syntax you want is:

ALTER TABLE <table_name>
ADD CONSTRAINT pk_table_name
PRIMARY KEY <comma_delimited_list_of_column_names> USING INDEX
PCTFREE <integer_value>
TABLESPACE <tablespace_name>;

Same goes for creating tables. Always specify PCTFREE, PCTUSED, and TABLESPACE name.

2. Never create anything while logged in as SYS or SYSTEM.

3. To see any object created you must either be the user that created the object (the schema owner) or you must be granted permission to see it. Oracle won't tell you an object exists that you can't see. The security model is such that it denies that the object exists.

If you created your table as SYS or SYSTEM log back in as the owner and:

DROP TABLE cde CASCADE CONSTRAINTS;

Then log in as another user and recreate the table using proper syntax.

For another user to see that the table exists:

GRANT SELECT ON <table_name> TO <schema_name>;

For more information on all of this go to http://tahiti.oracle.com. Or, preferably, take classes and support your local book seller.

Daniel Morgan Received on Fri Feb 14 2003 - 10:09:49 CET