traversing tablespaces

[lavieille]

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???

[lavieille]

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

[Prasy]

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.

[Prasy]

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.

[lavieille]

Prasy,

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

Thanks

[Prasy]

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

[lavieille]

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.

[Prasy]

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!!!!!!

[lavieille]

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.

[Prasy]

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.