Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    India
    Posts
    217

    Unanswered: creating table on a specific table space

    I have a user with sysdba privilage (rl_sys). I have logged in
    as rl_sys and created a new user rl_test.

    I have also created a table space rl_ts. For rl_test i have given
    the default table space as rl_ts.

    when i give the following command,
    grant create any table to rl_test
    Tables are getting created in the table space assigned to rl_sys, not rl_ts.

    What shouild i do, so that tables created by rl_test should be on rl_ts?

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    create table xyz (x number(10) tablespace new_tablespace;

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Are the tables being created in the rl_test schema? The default tablespace is tied ultimately to the schema owner - not the user creating the table - when that user has authority to create a table within any schema ('create any table').

    -Chuck

  4. #4
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Quote Originally Posted by chuck_forbes
    Are the tables being created in the rl_test schema?
    YES.

    Quote Originally Posted by chuck_forbes
    The default tablespace is tied ultimately to the schema owner - not the user creating the table.....
    -Chuck
    What is the difference between schema owner and user creating the table...are they not same?

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    They are not the same.

    Code:
    SQL> connect forbesc@dev
    Enter password: *******
    Connected.
    SQL> create table table_in_my_schema (f1 number);
    
    Table created.
    
    SQL> create table hr.table_in_my_schema (f1 number);
    
    Table created.
    
    SQL> select owner, table_name 
      2  from dba_tables
      3  where table_name = 'TABLE_IN_MY_SCHEMA';
    
    OWNER                          TABLE_NAME
    ------------------------------ ------------------------------
    HR                             TABLE_IN_MY_SCHEMA
    FORBESC                        TABLE_IN_MY_SCHEMA
    Ultimately, the owner of the table is the schema owner. There may be no ties to the user account which created the object.

    Are you sure that the table was created within the RL_TEST schema? Try logging in as RL_TEST and issuing:
    Code:
    SQL> select table_name, tablespace_name
      2  from user_tables;
    If RL_TS is actually not the tablespace being used, and RL_TS is the default tablespace for RL_TEST, then I'd be suspicious that the tool you're using might be setting the tablespace behind the scenes for you (by submitting DDL like marist89 posted).

    -Chuck

  6. #6
    Join Date
    Feb 2005
    Location
    India
    Posts
    217
    Chuck Thank you very much for the detailed explanation! I am clear now about my doubt. My tables were not getting created in rl_test schema!

    I didn't know that the owner of the table is schema owner and there is no link to the user who created the tables! That was great. Thank you.

Posting Permissions

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