traversing tablespaces
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: traversing tablespaces

  1. #1
    Join Date
    Feb 2001
    Posts
    16

    Thumbs down

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


  2. #2
    Join Date
    Feb 2001
    Posts
    16

    Thumbs down

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

  3. #3
    Join Date
    Feb 2001
    Posts
    83
    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.

    with regards
    Prasanna S

  4. #4
    Join Date
    Feb 2001
    Posts
    83
    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.
    with regards
    Prasanna S

  5. #5
    Join Date
    Feb 2001
    Posts
    16
    Prasy,

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

    Thanks

  6. #6
    Join Date
    Feb 2001
    Posts
    83

    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
    with regards
    Prasanna S

  7. #7
    Join Date
    Feb 2001
    Posts
    16
    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.




  8. #8
    Join Date
    Feb 2001
    Posts
    83
    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!!!!!!
    with regards
    Prasanna S

  9. #9
    Join Date
    Feb 2001
    Posts
    16

    Smile

    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.

  10. #10
    Join Date
    Feb 2001
    Posts
    83
    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.
    with regards
    Prasanna S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width