3

When you create an object in Oracle, and do not specify a schema (object prefix), is there a name for that default schema? I see in Oracle SQL Developer, that a newly created object shows up in the root object nodes when no prefix is specified, rather than the schema object nodes.

Creating table under schema X:

X.table_1

Creating table under default schema:

table_1

2
  • It's the current user's schema (= the name of the current user)
    – user1822
    Mar 11, 2016 at 16:22
  • 2
    The object will be created in the schema of whatever user you are logged in as at the time. Oracle's concept of "schema" is a bit different than other databases like, say, SQL Server. A database user has exactly 1 schema that has the same name as the user. Mar 11, 2016 at 16:22

1 Answer 1

6

By default, it is the user you used to log in, and you can query it as below:

select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;

You can change your current schema in your session however:

alter session set current_schema=USER2;

If you create a table after this, without specifying the schema, that table will be created in the USER2 schema regardless of what user you logged on with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.