:

Oracle Schema – What is an Oracle Schema? Is it a User?

Oracle database schema

What is an Oracle Schema?

In simple terms a schema in an Oracle database is another name for a user. So a schema and a user are the same thing.

SCHEMA = USER

The purpose of a schema in Oracle is to house database objects. The objects could be physical segments, like tables and indexes, or object definitions like views, packages, triggers, etc. Every object must belong to a schema and an object can belong to only one schema. However, the same object, with the same name can be created in many different schemas within the same database. Hopefully this image will help you to understand the relationship between a database, a schema, the segments in a schema and the tablespace in which the segments reside.

Oracle database schema

The Oracle database and schema

I’d now like to cover some of the key points about the various objects within the above image. This is in no way a complete list, just the important parts about each of the items which you guys should definitely know and then do some more reading about.

The Oracle Database

  • The database houses everything There is 1 database which has multiple users, tablespaces, schemas and segments
  • The create database statement defines some parameters which cannot be changed easily, such as the characterset
  • If there is any chance that you will have multi-language characters in your database, always use UTF8

Oracle Tablespace

  • This is a logical structure, meaning that a tablespace is not a physical object
  • A tablespace is made of 1 or more physical structures called datafiles
  • Each tablespace can have different characteristics, such as extent size and how the extents are managed
  • They are used to group segments (tables, indexes, etc) into logical groups. For example, you may have accounting data in one tablespace and reporting data in another. This does not have to be the case, though, you are free to put whatever you like in the tablespaces you create
  • The SYSTEM and SYSAUX tablespaces are mandatory as of 10g, and it is recommended to not put your own user created segments in either of these tablespaces

Datafiles

  • A datafile is a physical file on disk, just like any other file which sits on a hard disk but in an Oracle format
  • The datafile is created as part of a tablespace, and only one tablespace
  • You can set datafiles to autoextend which means they they become full they will automatically extend preventing users from running out of space
  • For performance reasons, I would recommend having fewer, larger datafiles rather than lots of small ones

The Oracle Schema or User

  • Oracle Schema and user are synonymous and the terms are usually used interchangeably
  • There can be thousands of users within one database
  • The schema owns segments (tables, indexes, etc) and objects (views, constraints, etc) and each segment/object can belong to only one schema
  • Users can see segments and objects in other schemas if they have been given the appropriate permissions
  • If you have many schemas it is a good idea to use Oracle roles to manage what privileges they have

Other Notes

I have included the statements for creating a database, schema, tablespace, table and user in the diagram. They are syntactially correct, so you can use them as examples if you like. I would recommend reading the Oracle Database Concepts Guide if you have not done so already, as there is a wealth of information in it.

This post is by no means a complete reference, the Oracle documentation does a great job of explaining it in detail. My thoughts behind this post were to try and clarify what a database schema and user are. If you are still unsure or have any questions please leave a comment below and I will do my best to answer you.

Rob

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *