Results 1 to 8 of 8

Thread: Ora Users

  1. #1
    Join Date
    May 2011
    Posts
    36

    Unanswered: Ora Users

    1. How to list all the user names in Oracle database.

    2. How to move the tables from User A to User B.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >1. How to list all the user names in Oracle database.
    SELECT USERNAME FROM DBA_USERS;
    >2. How to move the tables from User A to User B.
    while logged in as UserB do as below
    CREATE TABLE FOOBAR AS SELECT FROM A.FOOBAR

    when all else fails Read The Fine Manuals at
    Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2011
    Posts
    36
    Thanks for immediate reply.

    I think the below query will move all the tables from user A.

    CREATE TABLE FOOBAR AS SELECT FROM A.FOOBAR.

    What will be the query If we want to move selected tables

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I think the below query will move all the tables from user A.
    SQL For Dummies by Allen G. Taylor (Paperback - Feb 2, 2010)
    Paperback: 456 pages
    Publisher: For Dummies; 7 edition (February 2, 2010)
    Language: English
    ISBN-10: 0470557419
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2011
    Posts
    36
    Thanks anacedent.

    Now I able to move tables from one user to another.

    I want to know some things more...Pls clear me

    1. Is it not possible to move if the tables has Primary, foreign key..settings


    2. How to log on as user B while we are logged in as user A.

  6. #6
    Join Date
    May 2011
    Posts
    36
    How to log on as user B while we are logged in as user A.
    (Switching over between one user to another user)

    Thanks,
    Ananth

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As of your second question (from the initial post): what do you call "moving" tables? If MOVE is (as on MS Windows) "cut from this place, paste to another place" (i.e. they are removed from the source schema), then the above CTAS (CREATE TABLE AS SELECT) won't do the job because - tables will remain in the source schema.

    If you really want to move them, a question: why would you want to do that?

    Furthermore, if it is just a single table (or a few of them), then CTAS might be OK. However, as you noticed, constraints and indexes are not created with this technique. If there are many tables or you need constraints as well, perhaps you should take a look at Export and Import utilities. As far as I understood the discussion, I'd even SUGGEST you use EXP/IMP. At the operating system command prompt, you'd first export source schema:
    Code:
    exp scott/tiger@ora10 file=scott.dmp
    and then import it into the target schema:
    Code:
    imp mike/lion@ora10 file=scott.dmp full=y

    As of your last question: that depends on a tool you use. In SQL*Plus, you'd issue CONNECT command to switch to another user:
    Code:
    SQL> show user
    USER is "SCOTT"
    SQL> connect mike/lion@ora10
    Connected.
    SQL> show user
    USER is "MIKE"
    SQL>

  8. #8
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Quote Originally Posted by GaneshAnanth View Post
    2. How to move the tables from User A to User B.
    Why would you want to do that?
    1. You want user B to be able to access that table - take a look at GRANT statement
    2. You want to stop user A of being able to access that table - then you should move the table.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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