Results 1 to 4 of 4

Thread: Create table as

  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Create table as

    CREATE TABLE X002548.USERTAB AS SELECT * FROM USERTAB

    I was able to do this within the same "Connection"(?) but the above fails

    X002548 is a valid user in the "instance"(?)

    While this one works

    CREATE TABLE USERTAB2 AS SELECT * FROM USERTAB


    Do you guys use Oracle SQLDeveloper...or is there a better interface?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    What's the error message? Maybe you don't have the apppriate privileges to create a table in the X002548 schema. --=cf

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that you are trying to create a table in one schema which should be the same as a table in another schema. If so, the usual (and easy) way is to
    a) grant required privileges from table owner to another user
    b) connect as another user
    c) create table
    Something like this: I'll connect as Scott, create a table and grant SELECT privilege to user named Mike:
    Code:
    SQL> connect scott/tiger
    Connected.
    
    SQL> create table scotts_table
      2  (id number,
      3   value number);
    
    Table created.
    
    SQL> insert into scotts_table (id, value) values (1, 1000);
    
    1 row created.
    
    SQL> grant select on scotts_table to mike;
    
    Grant succeeded.
    Now connect as Mike and create table based on Scott's table:
    Code:
    SQL> connect mike/lion
    Connected.
    
    SQL> create table mikes_table as select * from scott.scotts_table;
    
    Table created.
    
    SQL> select * from mikes_table;
    
            ID      VALUE
    ---------- ----------
             1       1000
    
    SQL>
    It appears that you were doing almost the same, but driving in the wrong direction: you can't create a table in Mike's schema when connected as Scott (unless you grant some other privileges, but that is far more complicated than the approach described above).

    However, if I was wrong, what did you do? Could you post your SQL*Plus session?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Brett Kaiser View Post
    I was able to do this within the same "Connection"(?) but the above fails
    User or schema is more common.
    Quote Originally Posted by Brett Kaiser View Post
    X002548 is a valid user in the "instance"(?)
    Database is more common.
    Quote Originally Posted by Brett Kaiser View Post
    Do you guys use Oracle SQLDeveloper...or is there a better interface?
    As this is the only proper question you asked and nobody answered it, I will try to show my preferences. However, other Oracle developers may use different tools for the same actions.
    For querying data or exploring objects in database, I use SQL Developer - I just do not need to care about result set length (for queries) or exact definition of data dictionary (for objects/data). However, when running DDL and/or DML statements (anything changing structure/data), firstly I write all statements into file(s). Then I run the file in sqlplus and, after finishing, I analyze its output. Doing it this way, it is also easy to run it multiple times for different users/databases. Or (as you were asked by Littlefoot) just copy the output here - you do not have to remember, what statements you issued and how SQL Developer responded.

Posting Permissions

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