 |
|
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.
