Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    11

    Unanswered: tableaspace change

    Hi,
    I created a user as below
    create user paymaster identified by paymaster
    default tablespace users
    temporary tablespace temp

    this created user paymaster.
    however when i queried
    select owner,tablespace_name from dba_tables
    the output was as below:-

    OWNER TABLESPACE_NAME
    PAYMASTER SYSTEM

    the paymaster should be in tablespace 'PAYDATA'
    this tablespace is already created.
    How do i create user PAYMASTER to use PAYDATA?

    i had earlier dropped user paymtr from the database as the client wants paymtr to be changed to paymaster.
    I created as above and granted the roles.
    however when i tried to import from *.dmp file that was exported from paymaster in another machine(which was in system tablespace) and i queried from dba_tables to find all tables are in system tablespace.
    How can i retify this?

    please advise.

    thanks.

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Dear jeyagowri,

    If the tables to be exported are stored in SYSTEM tablespace in Source DB and if you import them in Targed db, these tables will be stored in the SYSTEM tablespace of the target DB, no matter which tablespace the target DB user is assigned to.

    If you want to move tables to Users default tablespace or whereever you want, You can use ALTER TABLE...MOVE command to perform these task.

    Thanks,

    Hings

    =======

    Still Learning...

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Dear jeyagowri,

    One more thing,

    If you want to change users default tablespace to PAYDATA,

    you can user following statement..

    ALTER USER paymaster DEFAULT TABLESPACE PAYDATA;

    Thanks,

    Hings

    ===========

    Still Learning...

Posting Permissions

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