Managing Tables

Objectives

After completing this lesson, you should be able todo the following:• Create tables using appropriate storage settings• Control the space used by tables• Analyze tables to check integrity and migration• Retrieve information about tables from the data

dictionary• Convert between different formats of ROWID

Storing User Data

Regular

table

Cluster

Partitioned

table

Index-organized

table

Structure of a Row

Database block

Row header

Column length

Column value

Oracle Data Types

CHAR(N), NCHAR(N)

VARCHAR2(N),

NVARCHAR2(N)

NUMBER(P,S)

DATE

RAW(N)

BLOB, CLOB,

NCLOB, BFILE

LONG, LONG RAW

ROWID, UROWID

VARRAY

TABLE

REF

Data type

Built-inUser-defined

Scalar RelationshipCollection

Data Types for Storing Large Objects

LONG, LONG RAW

Single column per table

Up to 2 gigabytes

SELECT returns data

Data stored in-line

No object type support

Sequential access to chunks

LOB

Multiple columns per table

Up to 4 gigabytes

SELECT returns locator

Data stored in-line or out-of-line

Supports object types

Random access to chunks

ROWID Data Type

• Unique identifier for a row • Used to locate a row

OOOOOO BBBBBBFFF RRR

Data object

number

Relative file

number

Row numberBlock number

ROWID Format

Restricted ROWID

• Can identify rows within a segment• Needs less space

BBBBBBBB FFFFRRRR

Block number Row number File number

. .

Collections

• Collections are objects that contain objects.• VARRAYs are ordered sets of elements containing

a count and a limit.• Nested tables are tables with a column or

variable of the TABLE data type.

VARRAY Nested

table

Creating a Table

CREATE TABLE employee(

id NUMBER(7),

last_name VARCHAR2(25),

dept_id NUMBER(7))

PCTFREE 20 PCTUSED 50

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE data;

Temporary Tables

• The rows are private to the session.

• Tables retain data only for the duration of a transaction or session.

• DML locks are not acquired on the data.• DMLs do not generate redo logs.

ON COMMIT PRESERVE ROWS;

CREATE GLOBAL TEMPORARY TABLE employee_temp

AS SELECT * FROM employee;

Creating a Table: Guidelines

• Use a few standard extent sizes for tables to reduce tablespace fragmentation.

• Use locally managed tablespaces to avoid fragmentation.

• Use the CACHE clause for frequently used, small tables.

Setting PCTFREE and PCTUSED

• Compute PCTFREE

(Average Row Size - Initial Row Size) * 100

Average Row Size

• Compute PCTUSED

Average Row Size * 100

100 - PCTFREE -

Available Data Space

Row Migration and Chaining

Before update After update

Pointer

Changing Storage and Block Utilization Parameters

ALTER TABLE summit.employee

PCTFREE 30

PCTUSED 50

STORAGE(NEXT 500K

MINEXTENTS 2

MAXEXTENTS 100);

Manually Allocating Extents

ALTER TABLE summit.employee

ALLOCATE EXTENT(SIZE 500K

DATAFILE ‘/DISK3/DATA01.DBF’);

Nonpartitioned Table Reorganization

ALTER TABLE employee

MOVE TABLESPACE data1;

• Moves data into a new segment while preserving indexes, constraints, privileges, and so on, on the table

• Is being used to move a table to a different tablespace or to reorganize extents

Free space

after delete

Unused block

Extent ID 0 1 2 3 4

Extent ID 0 1 2 3 4

Used block

High-Water MarkAfter inserts:

After deletes:High-water

mark

Finding the High-Water Mark: DBMS_SPACE.UNUSED_SPACE

Extent ID 0 1 2 3 4

High-water mark

LAST_USED_EXTENT_FILE_ID,

LAST_USED_EXTENT_BLOCK_ID

TOTAL_BLOCKS

UNUSED_BLOCKS

Free space

after delete

Unused block

Before

deallocation

Used block

Deallocation of Unused Space

ALTER TABLE summit.employee

DEALLOCATE UNUSED;

After

deallocation

High-water

mark

Extent ID 43210

43210Extent ID

Truncating a Table

TRUNCATE TABLE summit.employee;

Extent ID 0 1

High-water

mark

Free space

Dropping a Table

DROP TABLE summit.department

CASCADE CONSTRAINTS;

Dropping a Column

ALTER TABLE employee

DROP COLUMN comments

CASCADE CONSTRAINTS CHECKPOINT 1000;

Remove a column from a table

• This removes the column length and data from each row, freeing space in the data block.

• Dropping a column in a large table takes a considerable amount of time.

Using the UNUSED Option

ALTER TABLE orders

SET UNUSED COLUMN comments

CASCADE CONSTRAINTS;

• Drop unused columns

ALTER TABLE orders

DROP UNUSED COLUMNS CHECKPOINT 1000;

• Mark a column as unused

• Continue to drop column operation

ALTER TABLE orders

DROP COLUMNS CONTINUE CHECKPOINT 1000;

Retrieving Table InformationDBA_OBJECTSOWNER

OBJECT_NAME

OBJECT_ID

DATA_OBJECT_ID

CREATED

DBA_SEGMENTSOWNER

SEGMENT_NAME

TABLESPACE_NAME

HEADER_FILE

HEADER_BLOCK

DBA_TABLESOWNER

TABLE_NAME

PCT_FREE

PCT_USED

INITIAL_EXTENT

NEXT_EXTENT

MIN_EXTENTS

MAX_EXTENTS

PCT_INCREASE

CACHE

BLOCKS

EMPTY_BLOCKS

CHAIN_CNT

TEMPORARY

DURATION

DBA_EXTENTS

• OWNER

• SEGMENT_NAME

• EXTENT_ID

• FILE_ID

• BLOCK_ID

• BLOCKS

Retrieving Extent Information

DBMS_ROWID Package

Function Name

ROWID_CREATE

ROWID_OBJECT

ROWID_RELATIVE_FNO

ROWID_BLOCK_NUMBER

ROWID_ROW_NUMBER

ROWID_TO_ABSOLUTE_FNO

ROWID_TO_EXTENDED

ROWID_TO_RESTRICTED

Description

Creates a ROWID from individual components

Returns the object identifier for a ROWID

Returns the relative file number for a ROWID

Returns the block number for a ROWID

Returns the row number for a ROWID

Returns the absolute file number for a ROWID

Converts a ROWID from restricted to extended

Converts a ROWID from extended to restricted

Commonly used functions:

Summary

In this lesson, you should have learned how to:• Create a table with appropriate storage and

block utilization parameters• Control table storage• Use the DBMS_ROWID package