EnterpriseDB Schemas Oracle Tips by Burleson Consulting

A database schema is a way to logically combine related objects.  In Oracle, a schema is also a database user.  In EnterpriseDB, a schema is simply a logical extension of the database.  The public schema is automatically created when you create a database and is available to everyone.  You can create your own schemas to store logically related procedures, tables or other objects.

A schema may or may not be automatically created for each user depending upon the option chosen in the Create User dialog box of Developer Studio or the form of the CREATE USER command.  Other than the automatically created public schema, your database does not need to contain any schemas at all.  A schema offers you a logical separation and allows you to refer to an object with the <schema>.<object> notation.

You create a schema with the CREATE SCHEMA command.

CREATE SCHEMA <schemaname>;

A schema, unlike a database or cluster, is strictly a data dictionary object.  By that, I mean that nothing is physically created at the operating system level.  A schema’s only purpose in life is to allow you to give a namespace to your database objects.

Creating a schema is useful when you are deploying applications.  If you want to create an entire application as a single transaction, you can combine your database object creating with your schema creation.  If any object creation fails, the entire schema transaction will fail.

CREATE SCHEMA hr
  CREATE TABLE employees (
    Name VARCHAR2(200),
    Address VARCHAR2(100) )
  CREATE VIEW employees_vw AS
    SELECT * FROM employees )
; 

edb=# CREATE SCHEMA hr
edb-#   CREATE TABLE employees (
edb(#     Name VARCHAR2(200),
edb(#     Address VARCHAR2(100) )
edb-#   CREATE VIEW employees_vw AS
edb-#     SELECT * FROM employees )
edb-# ;
ERROR:  syntax error at or near ")" at character 155
LINE 6:     SELECT * FROM employees )
                                    ^
edb=# desc hr.employees
Did not find any relation named "hr.employees".
edb=#

Notice that the entire command is ended with a semi-colon (;) and not each command.  If the create view were to fail for some reason, the table and the schema creation will be rolled back.  This is a very nifty piece of functionality to assist with deployment.

You can drop a schema with the DROP SCHEMA command.  The DROP SCHEMA command will not drop a schema that contains objects unless you use the optional CASCADE option.

DROP SCHEMA <foo>;
DROP SCHEMA <foo> CASCADE;

EnterpriseDB Tablespaces

An EnterpriseDB cluster has a default data directory where all databases will put their data.  This default data directory may be over-ridden with a tablespace.   A tablespace is a pointer to an alternate data storage location on disk.

Tablespaces are implemented using symbolic links, which means that only operating systems that support symbolic links support tablespaces.  Put simply, tablespaces are not supported on MS-Windows.

When you create a tablespace, it is available at the cluster level, which means it is available for all of the databases within that cluster.

A tablespace allows you the ability to spread your data files across multiple disks or disk sub-systems.  You may want to do this for performance or maintenance reasons.  Frequent usage would include separating indexes and tables.  In a data warehouse, you might partition your tables and put your older partitions on slower disks.  I will cover table partitioning in Chapter 3, SQL Primer.

You create a tablespace via the CREATE TABLESPACE command:

CREATE TABLESPACE <tablespace_name> LOCATION ‘disk_location’;

Once you have created the tablespace, you can use it in CREATE TABLE, CREATE INDEX, CREATE DATABASE and ADD CONSTRAINT commands.

You can only drop a tablespace if the tablespace is completely empty.  You must manually drop each item in the database that resides in that tablespace.  You may drop a tablespace using the DROP TABLESPACE command.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.