CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
bernie2436
bernie2436

Reputation: 23901

How do I change the default schema in sql developer?

I don't see any tables in the tables section of a database that I've linked to w/ SQL developer. The DB admin says I need to change the default schema in SQL developer. How do I do that?

enter image description here

Upvotes: 60

Views: 238015

Answers (11)

Ryan
Ryan

Reputation: 1

You can change it by going to Select Database Connection -> Advanced. For NAME put CURRENT_SCHEMA and then a value.

Upvotes: 0

Mikhael Loo
Mikhael Loo

Reputation: 631

When a new user is created in ORACLE, an empty work area for tables and views is also automatically created. That work area is called a 'Schema'. Because of the tightly coupled relationship between users and schemas, the terms are often used synonymously. SQL Developer will, by default, show the empty schema that belongs to the user you connected with if it is indeed empty.

However, if you click to expand the part of the tree titled 'Other Users', you'll see other users/schemas that your user has access to. In there, you may find the tables and views you are seeking if you select the correct user/schema. An object only lives in the schema that owns it.

Still, it would be nice if the application allowed us to pick our desired schema closer to the root of the tree instead of forcing us to go searching for it. Other answers have offered workarounds such as:

  1. Logging in as the desired user/schema to begin with.
  2. Using a different Tool.
  3. Ignoring the tree and just executing: alter session set current_schema = otheruser;
  4. Creating a startup script to set the schema every time the application loads.

In the end, I searched and found another free tool that seems to solve this particular usability issue called DBeaver.

It's all a bit confusing because the word schema is overloaded. Schema could also be used to describe the database of your application. To get more clarity, read more about the term schema as it is used in the ORACLE context.

Upvotes: 5

shekhar
shekhar

Reputation: 141

Alternatively, just select 'Other Users' one of the element shows on the left hand side bottom of the current schema.

Select what ever the schema you want from the available list.

enter image description here

Upvotes: 13

Andres Diaz De Valdes
Andres Diaz De Valdes

Reputation: 49

After you granted the permissions to the specified user you have to do this at filtering:

First step:

First Step to change default Tables

Second step:

Second Step to change default Tables

Now you will be able to display the tables after you changed the default load Alter session to the desire schema (using a Trigger after LOG ON).

Upvotes: 0

Brian Ogden
Brian Ogden

Reputation: 19212

This will not change the default schema in Oracle Sql Developer but I wanted to point out that it is easy to quickly view another user schema, right click the Database Connection:

enter image description here

Select the user to see the schema for that user

enter image description here

Upvotes: 13

Ram
Ram

Reputation: 2387

alter session set current_schema = otheruser;

should do the trick.

Upvotes: 115

Ranald McKillop
Ranald McKillop

Reputation: 21

If you log in as scott, but wish to see the tables for the HR schema, then you need to alter your session "alter session set current_schema=HR;"

If you do this all time, then you can create a startup script called login.sql with the above command in it, then tell SQL Developer to run this at startup via Tool/Preferences/Database

Upvotes: 2

Carlos Negron
Carlos Negron

Reputation: 962

Just right clic on the created connection and select "Schema browser", then use the filter to display the desired one.

Cheers.

Upvotes: 27

Nick.Mc
Nick.Mc

Reputation: 19184

I know this is old but...

I found this:

http://javaforge.com/project/schemasel

From the description, after you install the plugin it appears that if you follow the logical connection name with a schema in square brackets, it should connect to the schema by default.

It does but the object browser does not.

Oh well.

Upvotes: 4

wolφi
wolφi

Reputation: 8361

Just create a new connection (hit the green plus sign) and enter the schema name and password of the new default schema your DBA suggested. You can switch between your old schema and the new schema with the pull down menu at the top right end of your window.

Upvotes: 0

Simon
Simon

Reputation: 4485

I don't know of any way doing this in SQL Developer. You can see all the other schemas and their objects (if you have the correct privileges) when looking in "Other Users" -> "< Schemaname >".

In your case, either use the method described above or create a new connection for the schema in which you want to work or create synonyms for all the tables you wish to access.

If you would work in SQL*Plus, issuing ALTER SESSION SET CURRENT_SCHEMA=MY_NAME would set your current schema (This is probably what your DBA means).

Upvotes: 15

Related Questions