Forum Stats

  • 3,757,057 Users
  • 2,251,192 Discussions
  • 7,869,716 Comments

Discussions

Use a schema

M.Emmanuel
M.Emmanuel Member Posts: 304
edited Dec 25, 2016 5:10PM in SQL & PL/SQL

I have the following schema created in Oracle 11g r2 XE database via Oracle Application Express:

Schema name: 'FOO'

Schema password: 'foo_password'

ADMIN user: 'ADMIN'

ADMIN user password: 'foo_password_2'

I am trying to create tables in that schema via SQL Developer connection, but connection uses SYS.

Which would be the best way to execute that DDL under that schema?

Thanks,

Tagged:
Gbenga AjakayeM.Emmanuel

Best Answer

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 25, 2016 8:15AM Accepted Answer
    M.Emmanuel wrote:I have the following schema created in Oracle 11g r2 XE database via Oracle Application Express:Schema name: 'FOO'Schema password: 'foo_password'ADMIN user: 'ADMIN'ADMIN user password: 'foo_password_2'I am trying to create tables in that schema via SQL Developer connection, but connection uses SYS.Which would be the best way to execute that DDL under that schema?

    The BEST way to execute DDL is to be logged onto the database as the owner of the objects.

    BTW in Oracle there is no significant difference between SCHEMA & USER.

    How do I ask a question on the forums?

«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 25, 2016 8:15AM Accepted Answer
    M.Emmanuel wrote:I have the following schema created in Oracle 11g r2 XE database via Oracle Application Express:Schema name: 'FOO'Schema password: 'foo_password'ADMIN user: 'ADMIN'ADMIN user password: 'foo_password_2'I am trying to create tables in that schema via SQL Developer connection, but connection uses SYS.Which would be the best way to execute that DDL under that schema?

    The BEST way to execute DDL is to be logged onto the database as the owner of the objects.

    BTW in Oracle there is no significant difference between SCHEMA & USER.

    How do I ask a question on the forums?

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Dec 25, 2016 8:15AM
    M.Emmanuel wrote:I have the following schema created in Oracle 11g r2 XE database via Oracle Application Express:Schema name: 'FOO'Schema password: 'foo_password'ADMIN user: 'ADMIN'ADMIN user password: 'foo_password_2'I am trying to create tables in that schema via SQL Developer connection, but connection uses SYS.Which would be the best way to execute that DDL under that schema?Thanks,

    Why not just connect as the user you wish to own the tables? Ensure it has the privileges it needs to do it's job.

    SYS should be reserved for very few operations.

    M.EmmanuelM.Emmanuel
  • mathguy
    mathguy Member Posts: 10,066 Gold Crown
    edited Dec 25, 2016 8:18AM

    Create a new Connection in SQL Developer, for the new schema. Follow the manual, it's easy.

    https://docs.oracle.com/cd/E55747_01/appdev.41/e55591/sql-developer-concepts-usage.htm#GUID-3548F659-BB53-466A-849A-DFC0…

    You had the right instinct to ask - don't ever create new tables when logged in as SYS!

    M.EmmanuelM.Emmanuel
  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 25, 2016 8:52AM

    While logged in as SYS user, you can create table or tables under FOO schema using the steps below.

    So, in your case, you do like this:sql> conn / as sysdbasql > CREATE TABLE foo.Persons ( PersonID int);

    SQL> conn / as sysdba

    Connected.

    SQL> select table_name from dba_tables where OWNER = 'SCOTT';

    TABLE_NAME

    ------------------------------

    DEPT

    EMP

    BONUS

    SALGRADE

    SQL> show user;

    USER is "SYS"

    SQL>

    SQL>

    SQL> CREATE TABLE scott.Persons ( PersonID int);

    Table created.

    SQL> select table_name from dba_tables where OWNER = 'SCOTT';

    TABLE_NAME

    ------------------------------

    DEPT

    EMP

    BONUS

    SALGRADE

    PERSONS

    SQL>

    M.EmmanuelM.Emmanuel
  • mathguy
    mathguy Member Posts: 10,066 Gold Crown
    edited Dec 25, 2016 8:57AM

    Hi - did you notice that the OP asked specifically about SQL Developer? What's the point of showing him how to do this in SQL Plus? Presumably if he is able to connect as SYS using SQL Plus, he would be able to figure out on his own how to connect using the new username/password, also using SQL Plus, I am sure that isn't what he meant.

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Dec 25, 2016 9:01AM

    While connected to sys in sql developer, the OP can still execute the commands. My intention was to show that it was possible.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 25, 2016 9:09AM
    mathguy wrote:Hi - did you notice that the OP asked specifically about SQL Developer? What's the point of showing him how to do this in SQL Plus? Presumably if he is able to connect as SYS using SQL Plus, he would be able to figure out on his own how to connect using the new username/password, also using SQL Plus, I am sure that isn't what he meant.

    the real problem is independent of what ever utility OP is using!

    Gbenga Ajakaye
  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 25, 2016 9:20AM
    M.Emmanuel wrote:I am trying to create tables in that schema via SQL Developer connection, but connection uses SYS.

    https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2659418700346202574

    use of SYS & SYSTEM should be avoided

  • mathguy
    mathguy Member Posts: 10,066 Gold Crown
    edited Dec 25, 2016 9:20AM

    Seriously? Even if I am right and the "real problem" is how to create a new "Connection" in SQL Developer?

    How do you create a new "Connection" in SQL Developer, using SQL Plus?

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 25, 2016 9:26AM
    mathguy wrote:Seriously? Even if I am right and the "real problem" is how to create a new "Connection" in SQL Developer?How do you create a new "Connection" in SQL Developer, using SQL Plus?

    OP asked "Which would be the best way to execute that DDL under that schema?"

    It appears that OP knows how to connect to database using SQL Developer.

This discussion has been closed.