BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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 TABLE employees (
    Name VARCHAR2(200),
    Address VARCHAR2(100) )
  CREATE VIEW employees_vw AS
    SELECT * FROM employees )

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

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.


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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter