1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

No Tables in SQL Developer

Discussion in 'Server Administration and Options' started by razer0794, Mar 23, 2014.

  1. razer0794

    razer0794 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi,
    I am a complete beginner in Oracle 12 and I'm trying to install it for school.
    I have installed Oracle12c in Windows 7 and created a user. But when I start SQL Developer, I can't see the point "Tables" or "Tabellen"(German), like it is shown in many tutorials. Am I using the wrong user(SYSTEM)? I also tried SQL Plus, but it doesn't seem to recognize any SQL command I enter. I also tried SYSTEM as SYSDBA in SQL Plus, but nothing works.
    The goal would be to create and fill a table.
    What is missing?
     

    Attached Files:

  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. razer0794

    razer0794 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    PDBS must be replaced with my db name, right? If I do that, I get a message, that the table or view doesn't exist.

    I just discovered my stupid mistake, why I had no Tables in SQL Developer. I used ORCL as SID, instead of my DB name. Now I have many tables in sql developer(LOGMNR, MVIEW, REPCAT...). Should I just create a new Table there?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, it isn't replaced with your db name. V$PDBS lists all of the Pluggable DataBaseS in Oracle 12c; you need to run the query as-is to get any usable results.


    Create a table where? Ideally you would create a new user id and create any new tables in that account; the accounts you listed are not accounts you want to add personal or test tables to. I would do this:


    Code (SQL):



    SQL> GRANT CREATE SESSION TO <SOME username here> IDENTIFIED BY <SOME password here>;


     

    For example:


    Code (SQL):

    SQL> GRANT CREATE SESSION, CREATE TABLE TO bleezoflap IDENTIFIED BY yarpschnitzel;


    GRANT succeeded.


    SQL>
     

    You can now connect to bleezoflap and create new tables:


    Code (SQL):

    SQL> CONNECT bleezoflap/yarpschnitzel
    Connected.
    SQL> CREATE TABLE plorg(
      2     flummox NUMBER,
      3     ergfrit varchar2(40),
      4     grunder DATE);


    TABLE created.


    SQL>
     

    These are examples only and should not be used as-is to create a new user since the password is publicly posted. Change the username and password accordingly and you should be able to create a new user account so you can create tables in your database.
     
  5. razer0794

    razer0794 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Just tried it. I'm getting a reply, that no lines were chosen. Do I have to use another user?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Just tried what, exactly? The command creates an empty table. To populate it you need to insert data:


    Code (SQL):

    SQL> BEGIN
      2     FOR i IN 1..1000 loop
      3             INSERT INTO plorg(flummox, ergfrit, grunder)
      4             VALUES(i, 'Record '||i, sysdate+i);
      5     END loop;
      6
      7     commit;
      8
      9  END;
     10  /


    PL/SQL PROCEDURE successfully completed.


    SQL> SELECT * FROM plorg;


       FLUMMOX ERGFRIT                                  GRUNDER
    ---------- ---------------------------------------- ---------
             1 Record 1                                 25-MAR-14
             2 Record 2                                 26-MAR-14
             3 Record 3                                 27-MAR-14
             4 Record 4                                 28-MAR-14
             5 Record 5                                 29-MAR-14
             6 Record 6                                 30-MAR-14
             7 Record 7                                 31-MAR-14
             8 Record 8                                 01-APR-14
             9 Record 9                                 02-APR-14
            10 Record 10                                03-APR-14
            11 Record 11                                04-APR-14
    ...
       FLUMMOX ERGFRIT                                  GRUNDER
    ---------- ---------------------------------------- ---------
           991 Record 991                               09-DEC-16
           992 Record 992                               10-DEC-16
           993 Record 993                               11-DEC-16
           994 Record 994                               12-DEC-16
           995 Record 995                               13-DEC-16
           996 Record 996                               14-DEC-16
           997 Record 997                               15-DEC-16
           998 Record 998                               16-DEC-16
           999 Record 999                               17-DEC-16
          1000 Record 1000                              18-DEC-16


    1000 ROWS selected.


    SQL>
     

    You will probably need to alter the user you just created and set a quota on the USERS tablespace (the default for creating users) so that account can insert data into the table:


    Code (SQL):



    SQL> CONNECT / AS sysdba
    Connected.
    SQL> ALTER USER bleezoflap quota unlimited ON users;


    USER altered.


    SQL>
     

    Connect as your new user and data should be able to be inserted into your new table.
     
  7. razer0794

    razer0794 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Sorry, didn't see your edit ;)
    I just found out, that the CREATE Command is working. Even without the GRANT command. Don't know, why I didn't try that.
    I thought, if not even show databases; is working, create won't work either.
    Does show databases; not work in Oracle SQL?

    But thank you very much for your effort, maybe I'll need it sometime.

    Other Question: Can I use the Enterprise Manager to manage the database(creating or filling tables,...). I only found some control features in the web interface (localhost:5501/em). Is there another web interface? I saw some pictures in the web, with pages, that I haven't seen on my interface.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without the grant you likely won't be able to insert data into your table. The CREATE TABLE statement works because all it does (in the form I gave you) is establish the table name and structure.

    No, you cannot use Enterprise Manager to create tables or insert data; that is not its purpose or design.

    Seeing 'some pictures in the web' doesn't say where you saw them or in what context. There ARE tools available, such as SQL Developer, TOAD, and the like that can create tables and manage data. You will need to download one of those tools separately; such tools are not part of Enterprise manager.


    There is no 'databases' option to the show command, even in 12c. There is the PDBS option to show all of the pluggable databases configured on the server:


    show pdbs


    Please get familiar with google.com and tahiti.oracle.com as they can provide a wealth of information that can answer such questions.
     
  9. razer0794

    razer0794 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I know that this picture is from an older Oracle SQL version, but I thought they kept this functionality.
    Well, google isn't new for me :), but it's not always that easy to find what I need exactly. At least in terms of Oracle SQL, because it's new to me, but I'll try.

    EDIT: thx for the tahiti.oracle.com link, looks good
     

    Attached Files: