Change Current Schema

Your rating: None Average: 1 (1 vote)

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users to contain, or reference, their data. Schema objects include structures like tables, views, and indexes. To access tables not on your schema, you need to issue the "Schema Alter" command.

Lets go through an example and try to access a table in the HR schema from a different schema and see what happens.
table not found different schema

Now, lets alter our schema session and try again.

ALTER session
SET current_schema = HR;

SELECT * FROM employees;

And voila, table was found.
Alter change schema session

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest (not verified)
Re: Change Current Schema

Thanks Compshack!!! What is the difference between database and schema? Can Database contains any number of schemas?
At my client place we have production, Test and Dev databases. does these are databases are schemas? How can we identify weather it is a schema or database?

Zooz's picture
User offline. Last seen 10 years 42 weeks ago. Offline
Joined: 06/27/2008
Posts: 10
Re: Change Current Schema

It's nice to know this command and it comes in handy a lot of times.

But, you do not want to be switching schemas all the time. Typically the owner schema will grant the other users/schema select previliges; only the users that are allowed to see that data.

This way you can access that data from the schema you are currently logged into without having to switch back and forth.

Zooz's picture
User offline. Last seen 10 years 42 weeks ago. Offline
Joined: 06/27/2008
Posts: 10
Re: Change Current Schema

I can answer the difference between a database and a schema.

The database is a collection of many objects. For example Your company would be a database, and all the employess are the users/schemas. You can have as many users/schemas as you want in a database.

Each user would own different objects and have privilges and access to objects other users can't have.

To answer your question about whether the DEV, Test and production are different databases, they should be. And usually under all these 3 databases you will have the same users, because they should be similar to each other. If Dev, Test and production are 3 users under one database instance, then this a really bad desgin in my opinion.