Oracle User vs Oracle Schemas

  • Oracle schema = Oracle user + database objects owned by that user.
  • When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default with the same name as User
  • There is a one-to-one correspondence between Oracle user name and Oracle schema name.
  • While user = schema in most circumstances, that isn’t true all the time.
CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction.

Example: 

Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view.

SQL> CREATE SCHEMA AUTHORIZATION hr

Comments

Post a Comment

Popular posts from this blog

Having and Where Clause

Where and Having both are used to apply filter Difference: You can not use Aggregate function in where clause, like select * from employee where SUM(salary) > 5000; but you can use Aggregate function in having clause. But having has to be used in conjunction with " GROUPED BY " clause. GROUP BY, makes the list of group, say if you have applied two " Grouped by" select city,gender   from employee   grouped by city, gender; in above case, you will have below type of  data Group-1 id  name      city      gender salary 2   Dharmu  BLR    M         4000 8   deepak    BLR   ML       5000 Group-2 id  name      city      gender salary 1   rashmi    BLR    F          10000 3   Sargun    BLR...
Read more