Managing Tablespace

What is an Oracle Tablespace?

A tablespace is a logical storage unit – multiple application objects (e.g. tables) can be stored in one tablespace. A tablespace can be online or offline (not accessible), and can contain one or more datafiles, each of which can be online or offline.

There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.

Locally Managed Tablespaces (LMT’s) : When creating an LMT, the storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not required (invalid syntax). An LMT can have either uniform or variable extent sizes. Variable extents are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.

For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Temporary tablespaces that manage their extents locally can only use this type of allocation.

For system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.

The following statement creates a locally managed tablespace named test_ts and specifies AUTOALLOCATE:

SQL Code :
CREATE TABLESPACE test1_ts DATAFILE '/u02/oracle/data/test1_ts.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Automatic segment-space management
SQL Code :
CREATE TABLESPACE test2_ts DATAFILE '/u02/oracle/data/test2_ts.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO;

Dictionary Managed Tablespaces: A tablespace that uses the data dictionary to manage its extents has incremental extent sizes, which are determined by the storage parameters INITIAL, NEXT, and PCTINCREASE. These can be adjusted to control the extent sizes. When additional space is needed, the NEXT and PCTINCREASE parameters determine the sizes of new extents.

As an example, the following statement creates the tablespace tbsa, with the following characteristics:

The data of the new tablespace is contained in a single datafile, 50M in size.

The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.

The default storage parameters for any segments created in this tablespace are specified.

The following statement creates the tablespace tbsb:

SQL Code :
CREATE TABLESPACE tbsb
    DATAFILE '/u02/oracle/data/tb01.dbf' SIZE 50M
    EXTENT MANAGEMENT DICTIONARY
    DEFAULT STORAGE (
        INITIAL 50K
        NEXT 50K
        MINEXTENTS 2
        MAXEXTENTS 50
        PCTINCREASE 0);

Temporary tablespace: Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except that:

• You cannot create a tempfile with the ALTER DATABASE statement.
• You cannot rename a tempfile or set it to read-only.
• Tempfiles are always set to NOLOGGING mode.
• When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (for example, UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.

Create Temporary Tablespace

SQL Code :
CREATE TEMPORARY TABLESPACE test_temp TEMPFILE '/u02/oracle/data/test_temp01.dbf'
     SIZE 20M REUSE
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

System Tablespace: Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database. All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides in the SYSTEM tablespace.

The SYSTEM tablespace is always online when the database is open.

Data Dictionary Objects to get information about tablespace

Tablespaces                    ts$, dba_tablespaces ,
                               user_tablespaces, Tablespace

Quotas                         tsq$, dba_ts_quotas, user_ts_quotas

Data Files                     dba_data_files, v_$backup_datafile,
                               v_$datafile,
                               v_$datafile_copy, v_$datafile_header

Free Space                     dba_free_space

Segments                       dba_segments, v_$segment_statistics

Extents                        dba_extents

Blocks                         v_$database_block_corruption

Groups                         dba_tablespace_groups

SYSAUX Tablespace              v_$sysaux_occupants

Temp Tablespace                dba_temp_files

Undo Tablespace                dba_rollback_segs, dba_undo_extents,
                               v_$rollstat, v_$undostat

Transportable Tablespaces      transport_set_violations

Dictionary Management          fet$, uet$

Monitoring Tablespace

To list the names and default storage parameters of all tablespaces in a database

SQL Code:
SELECT TABLESPACE_NAME "TABLESPACE",
   INITIAL_EXTENT "INITIAL_EXT",
   NEXT_EXTENT "NEXT_EXT",
   MIN_EXTENTS "MIN_EXT",
   MAX_EXTENTS "MAX_EXT",
   PCT_INCREASE
FROM DBA_TABLESPACES;
----------------------------------------------------------------------
SQL Code:
SELECT tablespace_name,
       block_size,
       status,
       contents,
       retention,
       extent_management,
       allocation_type,
       plugged_in,
       segment_space_management
FROM dba_tablespaces;
----------------------------------------------------------------------
SQL Code:
SELECT tablespace_name,
ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB,
ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL,
ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
  0 TOTAL_MB, 0 MAX_MB
  FROM dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT tablespace_name, 0 CURRENT_MB,
  SUM(bytes)/1024/1024 TOTAL_MB,
  SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
  GROUP BY tablespace_name)
GROUP BY tablespace_name
----------------------------------------------------------------------
SQL Code:
SELECT  dd.tablespace_name tablespace_name,
        dd.file_name file_name,
        dd.bytes/1024 TABLESPACE_KB,
        SUM(fs.bytes)/1024 KBYTES_FREE,
        MAX(fs.bytes)/1024 NEXT_FREE
FROM    sys.dba_free_space fs, sys.dba_data_files dd
WHERE   dd.tablespace_name = fs.tablespace_name
        AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name; 
----------------------------------------------------------------------
SQL Code:
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;

Displaying Statistics for Free Space (Extents) of Each Tablespace

SQL Code:
SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
   COUNT(*)    "PIECES",
   MAX(blocks) "MAXIMUM",
   MIN(blocks) "MINIMUM",
   AVG(blocks) "AVERAGE",
   SUM(blocks) "TOTAL"
   FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

To list the names, sizes, and associated tablespaces of a database

SQL Code:
SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

Monitoring Free Space of a database

SQL Code:
SELECT  BLOCK_ID, BYTES, BLOCKS
     FROM  DBA_FREE_SPACE
     WHERE TABLESPACE_NAME = 'USERS'
     ORDER BY BLOCK_ID;

BLOCK_ID               BYTES                  BLOCKS               
---------------------- ---------------------- ----------------------
417                    1835008                224

Coalescing Free Space

Syntax:
ALTER TABLESPACE <tablespace name> COALESCE;

Availability of Datafiles or Tempfiles

Syntax: 
  ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}
  ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}
SQL Code:
ALTER TABLESPACE users OFFLINE NORMAL;

Dropping Tablespace

The following statement drops the users tablespace, including the segments in the tablespace

SQL Code:
DROP TABLESPACE users INCLUDING CONTENTS;

The following statement drops the USER tablespace and its associated datafiles:

SQL Code:
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

Get SQL Code of a tablespace

SQL Code: 
SELECT dbms_metadata.get_ddl('TABLESPACE', 'USERS')
FROM dual;

To see the default tablestace information

SQL Code: 
SELECT *
FROM props$
WHERE name LIKE '%DEF%'

TABLESPACE INFORMATION

Tablespace Name – Name of the tablespace

Initial Extent – Default initial extent size

Next Extent – Default incremental extent size

Min Extents – Default minimum number of extents

Max Extents – Default maximum number of extents

PCT Increase – Default percent increase for extent size

Status – Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)

Contents – Type of tablespace. This column will have ‘TEMPORARY’ (v7.3+) for dedicated temporary tablespaces, and ‘PERMANENT’ for  tablespaces that can store both temporary sort segments and permanent objects.

 
SQL Code:
Select TABLESPACE_NAME,
     INITIAL_EXTENT,
     NEXT_EXTENT,
     MIN_EXTENTS,
     MAX_EXTENTS,
     PCT_INCREASE,
     STATUS,
     CONTENTS
from  dba_tablespaces
order by TABLESPACE_NAME

Coalesced Exts

Tablespace Name – Name of tablespace

Total Extents – Total number of free extents in tablespace

Extents Coalesced – Total number of coalesced free extents in tablespace

% Extents Coalesced – Percentage of coalesced free extents in tablespace

Total Bytes – Total number of free bytes in tablespace

Bytes Coalesced – Total number of coalesced free bytes in tablespace

Total Blocks – Total number of free oracle blocks in tablespace

Blocks Coalesced – Total number of coalesced free Oracle blocks in tablespace

% Blocks Coalesced – Percentage of coalesced free Oracle blocks in tablespace

SQL Code:
Select TABLESPACE_NAME,
     TOTAL_EXTENTS,
     EXTENTS_COALESCED,
     PERCENT_EXTENTS_COALESCED,
     TOTAL_BYTES,
     BYTES_COALESCED,
     TOTAL_BLOCKS,
     BLOCKS_COALESCED,
     PERCENT_BLOCKS_COALESCED
from  dba_free_space_coalesced
order by TABLESPACE_NAME

Usage

Tablespace Name – Name of the tablespace

Bytes Used – Size of the file in bytes

Bytes Free – Size of free space in bytes

Largest – Largest free space in bytes

Percent Used – Percentage of tablespace that is being used – Careful if it is more than 85%

 
SQL Code:
Select a.TABLESPACE_NAME,
     a.BYTES bytes_used,
     b.BYTES bytes_free,
     b.largest,
     round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from      
     (
            select  TABLESPACE_NAME,
                    sum(BYTES) BYTES
            from    dba_data_files
            group   by TABLESPACE_NAME
     )
     a,
     (
            select  TABLESPACE_NAME,
                    sum(BYTES) BYTES ,
                    max(BYTES) largest
            from    dba_free_space
            group   by TABLESPACE_NAME
     )
     b
where  a.TABLESPACE_NAME=b.TABLESPACE_NAME
order  by ((a.BYTES-b.BYTES)/a.BYTES) desc

Users Default (SYSTEM)

Username – Name of the user

Created – User creation date

Profile – Name of resource profile assigned to the user

Default Tablespace – Default tablespace for data objects

Temporary Tablespace – Default tablespace for temporary objects

Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.

 
SQL Code:
select USERNAME,
     CREATED,
     PROFILE,
     DEFAULT_TABLESPACE,
     TEMPORARY_TABLESPACE
from dba_users
order by USERNAME

Objects in SYSTEM Tablespac

Owner – Owner of the object
Object Name – Name of object
Object Type – Type of object
Tablespace – Tablespace name
Size – Size (bytes) of object
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace

SQL Code:
Select OWNER,
     SEGMENT_NAME,
     SEGMENT_TYPE,
     TABLESPACE_NAME,
     BYTES
from  dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and   OWNER not in ('SYS','SYSTEM')
order  by OWNER, SEGMENT_NAME

Freespace/Largest Ext

Tablespace – Name of the tablespace

Total Free Space – Total amount (bytes) of freespace in the tablespace

Largest Free Extent – Largest free extent (bytes) in the tablespace

SQL Code:
select TABLESPACE_NAME,
       sum(BYTES) Total_free_space,
       max(BYTES) largest_free_extent
from   dba_free_space
group  by TABLESPACE_NAME

Managing Index

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Major Data Dictionary view to manage Index

DBA view describes indexes on all tables in the database. ALL view describes indexes on all tables accessible to the user. USER view is restricted to indexes owned by the user. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement.

·  DBA_INDEXES
·  ALL_INDEXES
·  USER_INDEXES

These views describe the columns of indexes on tables. Some columns in these views contain statistics that are generated by the DBMS_STATS package or ANALYZE statement. DBA_IND_EXPRESSIONS

·  DBA_IND_COLUMNS
·  ALL_IND_COLUMNS
·  USER_IND_COLUMNS

These views describe the expressions of function-based indexes on tables. DBA_IND_STATISTICS

·  ALL_IND_EXPRESSIONS
·  USER_IND_EXPRESSIONS

These views contain optimizer statistics for indexes.

INDEX_STATS Stores information from the last ANALYZE INDEX…VALIDATE STRUCTURE statement. INDEX_HISTOGRAM Stores information from the last ANALYZE INDEX…VALIDATE STRUCTURE statement. V$OBJECT_USAGE Contains index usage information produced by the ALTER INDEX…MONITORING USAGE functionality.

·  ALL_IND_STATISTICS
·  USER_IND_STATISTICS

Create an Index

Syntax: 
CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, . column_n)
  [ COMPUTE STATISTICS ];

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

The syntax for creating a function-based index is:

Syntax :
CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, . function_n)
  [ COMPUTE STATISTICS ];

SQL Code: 
CREATE INDEX ename_idx ON emp (UPPER(emane));

Rebuild Index Syntax

Syntax: 
alter index index_name rebuild;

Rebuild Index of multiple users

SQL Code: 
Select  'alter index ' || OWNER ||'.'||INDEX_NAME || ' rebuild;'
from    all_indexes
Where   Owner = 'USER NAME'
    OR  Owner = 'USER NAME'

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.

The syntax for collecting statistics on an index is:

Syntax :
ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;

Move Table and Index to Other Table Space

Assume that the name of the table space convention like user name and postfix by idx and ts

Syntax: 
ALTER TABLE schema.table_name MOVE TABLESPACE TABLESPACE_NAME;
 
SQL Code:
select 'ALTER TABLE ' || OWNER || '.' || TABLE_NAME || ' MOVE TABLESPACE ' || OWNER || ’ts’ ||';'
from ALL_TABLES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

Rebuild and move index to another tablespace. Also it can be used to migrate index to tablespace with diffrent blocksize.

If your table contains LONG column you must export and import data with exp/expdp, imp/impdb utilities.

Syntax: 
ALTER INDEX schema.index_name REBUILD TABLESPACE TABLESPACE_NAME;

SQL Code
select 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD TABLESPACE ' || OWNER || 'idx’;'
from ALL_INDEXES
WHERE OWNER IN ('USER NAME', 'USER NAME', 'USER NAME');

ORA-01502 index ‘string.string’ or partition of such index is in unusable state

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition.

This error code says your index is in an unusable state. It’s easy to find these indexes.

How to identify the index unusable

SQL Code:
select index_name, status, owner
from all_indexes
where STATUS = 'UNUSABLE';

How to rebuild index unusable

SQL Code:
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where STATUS = 'UNUSABLE';

Drop an Index

Syntax:
DROP INDEX index_name;