my notes...

Regular Tables

:

The default table. Rows can be stored in any matter depending upon the activity in the database.

Partitioned Tables

:

These enable the building of scalable applications.

Characteristics:

• Partitioned using range, hash, composite or list partitioning.

• Each partition is a segment and can located in a different tablespace.

• Special command available to manage partitions within a table.

• Useful for large table which can be queried & manipulated using several processes concurrently.

Index Organized Tables

:

It is like a heap table with primary key on one or more of its columns. However instead of maintaining two separate storages, an IOT maintains a single B-tree containing the primary key and the rest of the column values.

An overflow segment may exist due to PCTTHRESHOLD value being set and the result of longer row lengths requiring the overflow area.

Characteristics:

• provides fast key-based access to table data for queries involving exact matches and range searches.

• lower storage requirements as columns not duplicated in separate index.

• the remaining non-key columns are stored in the index unless the index entry becomes too large, whereby Oracle provides an OVERFLOW clause to handle the problem.

Clustered Tables

:

A cluster is made up of a table or group of table that share the same data blocks, which are grouped together because they share common columns and are often used together.

Characteristics:

• Clustering is a mechanism which is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it was stored in a regular table.

• Clusters normalizes the physical storage of the tables without affecting the logical structure.

• Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scan on clustered tables are generally slow.

• Updating one of the columns in the cluster key can cause row migration.

  Cluster key:

• Clusters have a cluster key which is used to identify the rows.

• The cluster key can consist of one or more columns.

• Tables in a cluster have columns corresponding to the cluster key.

• The cluster key is independent of the primary key. The tables in a cluster can have primary key which can be the cluster key or may be not.

Note : Display uses base-64 encoding scheme using following character sets : A-Z,a-z,0-9, and /. That is a total 64 characters.

Segment no.(Data object No.)

^{Points to tablespace} >

Tablespace (since a segment can reside in only one tablespace)

^{Relative file no.} >

Relative Datafile of the tablespace

^{Block no.} >

Block No.

^{Row ID .} >

Row directory entry for the row

^Row >

Beginning of the row

Notes :

• Display uses base-16 format.

• Prior to Oracle 7, Oracle required datafile numbers to be unique within the database

• Oracle 8.0.3 had introduced a 16 byte extended ROWID temporarily. Thereafter the 10bytes format was introduced.

Uses :

• Still used in non-partitioned indexes on non-partitioned tables where all index entries refer to rows within the same segment.

Notes :

• Columns are generally stored in the order in which they are defined.

• Any trailing NULL column are not stored. A single byte for column length is required for

• non-trailing NULL columns.

• Adjacent rows don't require any space in between them. Each row in a block has an entry in the row directory. The directory slot points to the beginning of the row.

Note : The above is a subset of the CREATE TABLE clause.

NEXT

:

PCTINCREASE

:

MINEXTENT

:

MAXEXTENT

:

PCTFREE

:

PCTUSED

:

Deprecated from Oracle 9i together with FREELIST, FREELIST GROUPS

INTRANS

:

MAXTRANS

:

TABLESPACE

:

Syntax

Example

ALTER TABLE table_name
{[storage_clause]
[INITRANS integer]
[MAXTRANS integer]}

ALTER TABLE hr.employees
PCTFREE 20
PCTUSED 60
STORAGE (NEXT 100K MINEXTENT 5 MAXEXTENT 50);

MINEXTENT

:

Can be changed to any value <= to the current no. of extents. Is has no immediate effect but will be used if the table is TRUNCATED.

MAXEXTENT

:

Can be changed to any value >= to the current no. of extents. It can also be set to UNLIMITED.

Restrictions:

• INITIAL value can't be modified.

• The value of NEXT will be rounded to a value that is a multiple of the block size >= to the value specified.

• Place tables in separate tablespace from those holding undo segments, indexes & temporarily segments.

• Use locally managed tablespaces to avoid fragmentation

• Use few standard extent sizes to reduce tablespace fragmentation

:

Rows only visible within transaction.

:

Rows visible during entire session.

PCTFREE

:

Average increase in row_size *100

Average row Size

i.e. The free space should be able to accommodate average row size increases

Note: Higher PCTFREE is used to accommodate updates within a database block. Higher PCTFREE results in lower block density- fewer rows per block.

Use higher value for:

• Columns that are initially NULL and latter filled up

• Columns that are likely to increase in size with updates.

PCTUSED

:

100 – PCTFREE - Average row size

i.e. Whenever there is space to accommodate a single new row.

Note: Set so that the block is returned to the free list only when there is sufficient space to accommodate an average row. If it does not, then Oracle Server looks up the 2^nd block on the free list. This linear scan continues until a block with sufficient space is found or the end of list is reached. The value of average row can be found using ANALYZE TABLE command.

Row Migration

:

When PCTFREE is low, there may not be sufficient space in the block to accommodate a row growth due to update. In such a situation Oracle server moves the entire row to a new block & leaves a pointer from the original block to the new location. This causes decrease in I/O performance associated with this row due to two data block scans.

Chaining

:

Row chaining occurs when a row is too large to fit into any block. Oracle server divides the row into smaller chunks called row pieces. Each row piece is stored in a block along with necessary pointers to retrieve and assemble the entire row. Row chaining can be minimised by choosing a higher block size, or by splitting a table into multiple tables with fewer columns.

Syntax

Example

ALTER TABLE [schema.] tablename
ALLOCATE EXTENT [([SIZE integer [K|M]]
[DATAFILE 'filename'] )]

ALTER TABLE hr.employees
ALLOCATE EXTENT (SIZE 400K
DATAFILE 'd:/Oracle/ora90/emp1.dbf');

Purpose:

• To control distribution of extents across files.

• Before loading data in bulk to avoid dynamic extension of tables.

Notes:

• If SIZE is omitted NEXT_EXTENT from DBA_TABLES is used.

• If DATAFILE clause is not used, Oracle will allocate the extent in one of the files in the tablespace containing the table.

• Specified DATAFILE must belong to the tablespace to which the table belongs. Otherwise an error is thrown.

• The NEXT_EXTENT value in DBA_TABLES will not be affected by the manual extent allocation. Oracle server will not recalculate the size of the next extent when this command is executed.

ALTER TABLE hr.employees
MOVE TABLESPACE sktab;

Using this command a non-partitioned table can be moved without using EXPORT or IMPORT utilility. In addition, storage parameters can be changed too.

Purpose:

• Moving a table from one tablespace to another.

• Reorganizing the table to eliminate row migration.

Note: Moving the tables requires rebuilding of indexed otherwise 'index in unusable state' error is thrown.

Syntax:

Example:

TRUNCATE TABLE [schema.] tablename
[{DROP|REUSE} STORAGE];

TRUNCATE TABLE hr.employees;

Characteristics:

• All rows in the table are deleted.

• No undo data is generated and the command commits implicitly, TRUNCATE TABLE being a DDL command.

• Corresponding indexes are truncated too.

• A table which is reference by a foreign key can't be truncated.

• The delete trigger don't fire when this command is used.

Syntax:

Example:

DROP TABLE [schema.] tablename
[CASCADE CONSTRAINTS];

DROP TABLE hr.departments
CASCADE CONSTRAINTS ;

Characteristics:

• When a table is dropped, the extents used by it are released. If they are contiguous they may be coalesced either automatically or manually at a latter stage.

• CASCADE CONSTRAINTS is necessary if the table is a parent table is a foreign key relationship.

ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;

Characteristics:

• Dropping a column cleans unused and potentially space demanding columns without export or import of data and recreate indexes & constraints.

• It can take significant amount of time as all the data for the column is deleted from the table.

• Requires a lot of undo space. CHECKPOINTS can be specified to minimize the use of undo space. The the above example, a checkpoint occurs every 1000 rows.

• The table is marked invalid until the operation continues.

• If the instance fails during the operation, the table remains invalid on startup, and the operation needs to be completed. To resume interrupted drop operation:

  ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

  The above command will generate an error if the table is in a valid state.

• Before 8i it was not possible to drop a column.

ALTER TABLE hr.employees
SET UNUSED COLUMN comments
CASCADE CONSTRAINTS;

ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;

ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;

Characteristics:

• Marking a column unused is relatively quick as it does not reclaim unused disk space as the data is not removed. Unused columns act as if they are not part of the table. They don't show up in the DESCRIBE command. A user can add a new column with the same name as an unused column.

• Can be especially useful when more than one columns needs to be dropped. Dropping each will require all rows of the table to be updated twice. Mark them as unused and then drop in one go.

• Dropping unused columns can be accomplished during off-peak hours to minimize load on the system.

Restrictions on dropping a Column.

Can't do the followings:

• drop a column from an object type table

• drop a column from nested tables

• drop all columns in a table

• drop a partition key column

• drop a column from tables owned by SYS

• drop a column from Index-Organized-Tables if the column is a primary key.

• A LONG or LONG RAW column that is unused but not dropped will prevent an add of a LONG or LONG RAW column to the table.

DBA_UNUSED_COL_TABS

DBA_PARTIAL_DROP_TABS

DBA_TABLES

DBA_OBJECTS

2:

Storage clause specified at segment level except for MINIMUM EXTENT & UNIFORM SIZE tablespace parameters.

1:

If not specified at segment level, default storage clause specified at tablespace level used

0:

If above two not specified than Oracle Server defaults are used

Note : Any alteration of staorage clause applies only to the extents not yet allocated

DB_2K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_32K_CACHE_SIZE

DB_4K_CACHE_SIZE

DB_16K_CACHE_SIZE

CREATE TABLESPACE sktab
DATAFILE 'sk01.dbf'
SIZE 10M BLOCKSIZE 4K;

• View : dba_tablespaces

Parameters

• INITRANS
• MAXTRANS
• PCTFREE
• PCTUSED

• FREELIST

Tracking in-segment free space using bitmaps instead of FREELISTs. This bitmap is stored in a separate set of blocks referred to as bitmapped blocks (BMBs). It describes the status of each block in the segment with respect to its availability for space.

Advantages :

• Ease of management : PCTUSED, FREELISTS, FREELIST GROUPS managed automatically.

• Better space utlization : All objects, especially those with varying row sizes, utilize space more effeciently.

• better performance for concurrent inserts.

Restrictions :

• Can't be used for tablespaces with LOBs.

Configuring :

CREATE TABLESPACE sktab
DATAFILE 'D:/Oracle/Ora90/sktab01.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

Notes :

• Once bitmap segments are specified at tablespace creation, it can't subsequently be altered. Any specification of PCTUSED, FREELISTS, FREELIST GROUPS is ignored.

• Segments that can be bitmap managed are regular tables, indexes, Index organised tables and LOBs.

DBA_EXTENTS

DBA_SEGMENTS

DBA_TABLESPACES

DBA_DATA_FILES

DBA_FREE_SPAC

tablespace

:

Name of the tablespace to be created

DATAFILE

:

Datafile(s) which make up the tablespace

filename [SIZE integer [K|M]] [REUSE] [autoextend_clause]

MINIMUM EXTENT

:

This ensures that every used extent size in the tablespace is a multiple of this integer in Kilobyte or Megabyte.

LOGGING

:

Any changes to tables, indexes & partition within the tablespace will be written to the redo. This is the default mode.

NOLOGGING

:

Opposite of LOGGING. NOLOGGING effects only some DML & DDL commands ex. direct loads.

DEFAULT

:

Default storage parameters for all objects created in the tablespace.

DEFAULT STORAGE (INITIAL integer [K|M]
NEXT integer [K|M] MAXEXTENTS integer)

Note :

• Only meant for Dictionary managed tablespace. This is the old way of doing things. It is a default setting to be used for objects creations that don't have a storage parameters defined for them.

OFFLINE

:

Makes the tablespace unavailable immediately after creation.

PERMANENT

:

The tablespace can be used to hold permanent objects.

TEMPORARY

:

The tablespace can be used to hold only temporary objects.

Can't specify EXTENT MANAGEMENT LOCAL or BLOCKSIZE clause.

extent_management_clause

:

Specifies how the extents of the tablespace is to be managed.

[ENTENT MANAGEMENT [DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [SIZE integer [K | M]]]]

segment_management_clause

:

Relevant only for PERMANENT, LOCAL managed tablespaces. Free lists or bitmaps to track free & used space in the segment.

Eg: [SEGMENT SPACE MANAGEMENT AUTO ]

DATAFILE 'D:/Oracle/Ora90/findata01.dbf' SIZE 100M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE (initial 1M NEXT 1M PCTINCREASE 0);

Syntax

Example

CREATE UNDO TABLESPACE tablespace
[DATAFILE clause];

CREATE UNDO TABLESPACE undo1
DATAFILE 'D:/Oracle/Ora90/undo01.dbf'
SIZE 50M;

Syntax

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE 'D:/Oracle/Ora90/temp01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

During database creation:

CREATE DATABASE chik ......
....
....
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'D:/Oracle/Ora90/temp.dbf' SIZE 100M
....

>>>>When default Temporary Tablespace is created with the CREATE DATABASE, it is of locally managed type<<<<

After database creation:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

To find the temporary tablespace for the database (View : DATABASE_PROPERTIES):

SELECT * FROM DATABASE_PROPERTIES;

autoextend_clause for datafiles:

[AUTOEXTEND {ON|OFF[NEXT integer[K|M]]
[MAXSIZE UNLIMITED|integer[K|M]] } ];

CREATE TABLESPACE tablespace
DATAFILE 'D:/Oracle/Ora90/sk01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

ALTER DATABASE [database]
DATAFILE filename [, filename] ...
autoextend_clause;

Syntax

Example

ALTER DATABASE [database]
DATAFILE filename [, filename] ...
RESIZE integer[K|M];

ALTER DATABASE
DATAFILE 'D:/Oracle/Ora90/sk02.dbf'
RESIZE 200M;

Syntax

Example

ALTER TABLESPACE tablespace
ADD DATAFILE
filespec [autoextend clause] ;

ALTER TABLESPACE sktab
ADD DATAFILE 'D:/Oracle/Ora90/sk03.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 500M ;

Syntax 1:

Example

ALTER TABLESPACE tablespace
RENAME DATAFILE filename [, filename] ... TO
filename [, filename] ...;

ALTER TABLESPACE sktab
RENAME DATAFILE 'D:/Oracle/Ora90/sk03.dbf' TO SK_TAB03 ;

Syntax 2:

Example

ALTER DATABASE [database]
RENAME FILE filename [, filename] ... TO
filename [, filename] ...;

ALTER DATABASE
RENAME DATAFILE 'D:/Oracle/Ora90/sk03.dbf' TO 'D:/Oracle/Ora90/SK_TAB03';

Syntax :

Example

DROP TABLESPACE tablespace
[INCLUDING CONTENTS [AND DATAFILES]
[CASCADE CONSTRAINTS]] ;

DROP TABLESPACE sktab
INCLUDING CONTENTS AND DATAFILES;

INCLUDING CONTENTS

Drops the segments

INCLUDING CONTENTS AND DATAFILES

:

Deletes datafiles

CASCADE CONSTRAINTS

:

Drops all referential integrity constraints

Syntax :

Example

CREATE TABLESPACE tablespace

[DATAFILE [filename] [SIZE integer[K|M] ] ];

CREATE TABLESPACE sktab DATAFILES SIZE 50M;

ALTER TABLESPACE sktab ADD DATAFILE;

Tablespaces

:

DBA_TABLESPACES

V$TABLESPACE

:

:

Datafiles

:

DBA_DATA_FILES

V$DATAFILE

:

:

Select tablespace_name, file_name, autoextensible from DBA_DATA_FILES;

Temp files

:

DBA_TEMP_FILES

V$TEMPFILE

: