Oracle SQL Developer Query Connect as Other User

Question

I am designing a new database via a new sql file. I am using Oracle SQL Developer and logged in as sysdba.

I would like to know if SQL developer supports or has equivalent command to

connect scott/tiger;

Thank you in advance.

Edit

I am trying to create a foreign key on Schema A table referencing to Schema B table.

The error shown is

[Err] ORA-00942: table or view does not exist

I therefore want to login as "Schema B", grant all permission to SYS user, login as sys again and create the relationship.

Hope I am clearer this time.

Adam Musch · Accepted Answer · 2011-06-09 12:58:19Z

7

I believe you need to break and re-establish the connection to the database/service in order to log in as a different user in SQL Developer.

However, if you simply want to be in the SCOTT user's name space (so that CREATE TABLE EMP... is executed as CREATE TABLE SCOTT.EMP ..., you can issue

alter session set current_schema = SCOTT;

answered Jun 9, 2011 at 12:58

Adam Musch

13.7k3 gold badges30 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Binnet Over a year ago

Being new to Oracle, I think I have a mistake in the concept itself. I have edited the question to the specific problem I have.

DCookie · Accepted Answer · 2011-06-09 15:16:59Z

2

As the user SYS you should be able to create the relationship you want by simply prefixing object names with schema owner, as mentioned previously:

ALTR TABLE foo.t1 ADD FOREIGN KEY (col) REFERENCES bar.t2(ID);

That said, you must also grant the REFERENCES privilege to the user foo in order to make this relationship:

GRANT REFERENCES ON bar.t2 TO foo;

The user SYS should be able to do all this. Whether it's a good practice or not is another question.

answered Jun 9, 2011 at 15:16

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Comments

Phil · Accepted Answer · 2011-06-09 14:48:17Z

1

Also remember that if you're trying to create a FK to a table in a different schema & there aren't any synonyms, you have to fully qualify the name:

eg, as the user SCHEMA_A:

ALTER TABLE SCHEMA_A.TABLE_A
add CONSTRAINT fk_TABLE_B
  FOREIGN KEY (COLUMN_A)
  REFERENCES SCHEMA_B.TABLE_B(COLUMN_B);

You mention use of the SYS user - I hope you're not logging in a SYS to do normal development...

answered Jun 9, 2011 at 14:48

Phil

2,43219 silver badges22 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Developer Query Connect as Other User

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related