Oracle Concepts - Tablespaces

Oracle Tablespace Concepts

Tablespaces are the bridge between certain physical and logical components of the Oracle database. Tablespaces are where you store Oracle database objects such as tables, indexes and rollback segments.  You can think of a tablespace like a shared disk drive in Windows. You can store files on your shared drive, move files around and remove files. The same is true with tablespaces. 

A tablespace is made up of one or more database datafiles. The total amount of storage space available in a tablespace is the sum of the physical disk size of all the datafiles associated with that tablespace (less some system overhead). The datafiles are created automatically when the tablespace is defined. In most cases, all datafile space is pre-allocated; that is, the space is set aside when the datafile is created. Thus, when you create a tablespace, you define the initial size of the associated datafile. We will discuss the specifics of creating tablespaces later in this book.

Tablespaces are given names as they are created. For example, the first tablespaces that are created are named SYSTEM and SYSAUX (though SYSAUX is only created in 10g).

Tablespaces are generally named based on the objects within the tablespace.  For example you might name a tablespace PAYROLL_DATA if it?s going to store payroll related information. We will be discussing tablespaces in great detail in later chapters in this book.

Oracle Block Concept

As we discussed earlier, a block is the smallest unit of storage in Oracle. The size of a database block is fixed when the database is created, and can not be changed except by rebuilding the database from scratch. The database block size is fixed at 2K, 4K, 8K, 16K, or 32K in size. Once the base block size is defined, you can create new tablespace with alternate block sizes.

Remember that the Oracle instance also includes a RAM buffer cache which is made-up of RAM blocks which map to the data block in the physical datafiles.

Oracle Extents Concepts

An extent is an uninterrupted (or contiguous) allocation of blocks within a segment. Extents are assigned to a segment automatically by Oracle, so you will rarely deal directly with an extent, rather you will deal directly with its associated segment.

An extent must be on contiguous blocks within a single datafile, so an extent cannot span multiple Oracle datafiles. Oracle will allocate the size of the extents based on the type of tablespace. We will discuss more about extent allocation in later chapters when we discuss table and index creation. 

Oracle Segments Concepts

Segments are the storage objects within the Oracle database. A segment might be a table, an index, a cluster or any one of more than 20 object types.

The DBA creates the segments and assigns them to a specific tablespace. In most cases a single segment cannot reside in more than one tablespace. However, you can split up, or partition, a segment into different tablespaces.

After completing above introduction goto this link for tablespace creation

http://yasirdba.blogspot.com/2014/06/database-basic.html

Database basic

Create table space

create tablespace tbs_yasir

datafile 'C:\oraclexe\oradata\XE\yasir_01.dbf'

size 50m;

Create Temp table space

CREATE TEMPORARY TABLESPACE tbs_temp_yasir

TEMPFILE  'C:\oraclexe\oradata\XE\tbs_temp_yasir.dbf' 

SIZE 5M AUTOEXTEND ON;

CREATE A NEW USER IN ORACLE

CREATE USER yasir

  IDENTIFIED BY yasir123

  DEFAULT TABLESPACE tbs_yasir

  TEMPORARY TABLESPACE   tbs_temp_yasir  QUOTA 20M on tbs_yasir;

ASSIGN SYSTEM PRIVILEGES TO NEW USER IN ORACLE

GRANT create session TO yasir;

GRANT create table TO yasir;

GRANT create view TO yasir;

GRANT create any trigger TO yasir;

GRANT create any procedure TO yasir;

GRANT create sequence TO yasir;

GRANT create synonym TO yasir;

CREATE OBJECTS IN THE SCHEMA

The following is a CREATE SCHEMA statement (creating one table within the schema):

CREATE SCHEMA AUTHORIZATION yasir

    CREATE TABLE suppliers

( supplier_id number(10) not null,

  supplier_name varchar2(50) not null,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10)

);

CREATE SYNONYMS FOR OBJECTS

You may want to create synonyms so that other schemas can access the new database objects 

(ie: tables) without having to prefix the object names with the schema name.

For example, if you were another user named yasir and wanted to select from the suppliers 

table in new_schema, you would have to run the following SELECT statement (before any 

synonyms are created):

SELECT * 

FROM new_schema.suppliers;

If you then created a synonym for the suppliers table as follows:

CREATE PUBLIC SYNONYM suppliers_yasir

FOR new_schema.suppliers;

You could run the SELECT statement as follows:

SELECT * 

FROM suppliers;

No longer needing to prefix the table name with the schema name.

SOLUTION FOR PRACTICE EXERCISE #1:

CREATE TABLE SUPPLIERS

( SUPPLIER_ID NUMBER(10) NOT NULL,

  SUPPLIER_NAME VARCHAR2(50) NOT NULL,

  ADDRESS VARCHAR2(50),

  CITY VARCHAR2(50),

  STATE VARCHAR2(25),

  ZIP_CODE VARCHAR2(10)

);

PRACTICE EXERCISE #2:

Create an Oracle table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

SOLUTION FOR PRACTICE EXERCISE #2:

The Oracle CREATE TABLE statement for the customers table is:

CREATE TABLE customers

( customer_id number(10) not null,

  customer_name varchar2(50) not null,

  address varchar2(50),

  city varchar2(50),

  state varchar2(25),

  zip_code varchar2(10),

  CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

SOLUTION FOR PRACTICE EXERCISE #3:

CREATE TABLE DEPARTMENTS(
DEPARTMENT_ID NUMBER(10));

The Oracle CREATE TABLE statement for the employees table is:

CREATE TABLE employees

( employee_number number(10) not null,

  employee_name varchar2(50) not null,

  department_id number(10),

  salary number(6),

  CONSTRAINT employees_pk PRIMARY KEY (employee_number),

  CONSTRAINT fk_departments

    FOREIGN KEY (department_id)

    REFERENCES departments(department_id)

);