traversing tablespaces

Hi friends,

I have created three tablespaces: ASSET, ACCOUNTS, and LICENSING.

I want one user, INTEGRATION, to be able to INSERT, SELECT and UPDATE into the tables residing in these three tablespaces.

Is this possible? If so, how???

I'm trying to find a way to map this user to all three table spaces.

hi,

It is possible, U can use quota to specify tablespaces, a user can use apart from the default tablespace. This can be specified when u create a user.

U can specify the tablespace where u wanna create the object. For example, when u create a table u can specify a tablespace where u need to create a table.

Prasy,

Can you show me the code for this and for a SELECT statement, please?

Thanks

1)CREATE TABLE salgrade
( grade NUMBER CONSTRAINT pk_salgrade
PRIMARY KEY
USING INDEX TABLESPACE users_a,
losal NUMBER,
hisal NUMBER )
TABLESPACE human_resource ;


2)Selecting is normal as we perform for other objects

Prasy,

1)CREATE TABLE salgrade
( grade NUMBER CONSTRAINT pk_salgrade
PRIMARY KEY
USING INDEX TABLESPACE users_a,
losal NUMBER,
hisal NUMBER )
TABLESPACE human_resource ;

what is this user's default tablespace? can you give a simpler example?

i'm not clear on this example. itried your suggestion and it didn't work...

Help please.

when u create user

create user abc identified by abc default tablespace users temporary tablespace temp quota 5m on project.

So when u create a user in such manner the user can have 5m quota on project tablespace apart from default

when a create a object under that schema, when u don't specify tablespace it goes to default tablespace users, or when u specify tablespace while u create object under that schema its stored in that particular tablespace. So u map objects to the tablespace.

I hope u understood!!!!!!

Prasy,

Thank you for your help this morning/afternoon. Your suggestion works, I'm sure. The problem, as I've come to realized, is that I need to access tables in this tablespace that are owned by the user "ACCOUNT".

This is my delema. I think I need to grant rights of some sort to the new user (SYSTEM priveleges) so he can access the the specified tables.

I try to grant these OBJECT priveleges:
GRANT INSERT ON "ACCOUNTS"."ACCOUNT" TO "1"
GRANT SELECT ON "ACCOUNTS"."ACCOUNT" TO "1"
GRANT UPDATE ON "ACCOUNTS"."ACCOUNT" TO "1"
but I get feedback that SYSTEM priveleges are needed.

Thank you for your help so far. Anymore would be much appreciated.

Suppose the user is Account and table is employee. If user tempaccount need to access account.employee,
First login as Account and
grant insert on employee to tempaccount;
This works.

Check : After creating a user from internal , grant connect,resource to tempaccount.