LLI Wiki — My Digital Knowledge Base

Oracle: managing tablespaces and datafiles

Oracle queries and commands to monitor space, add, resize, move and drop tablespaces and datafiles.

A collection of Oracle Database queries and commands to inspect and manage tablespaces and datafiles: sizes, free space, growth, adding, resizing, moving and dropping files. Run them as a user with access to the DBA_* and V$ views.

Moving or renaming a datafile

ALTER DATABASE MOVE DATAFILE '/u01/oradata/ORCL/users01.dbf' TO '/u02/oradata/ORCL/users01.dbf';

Reference: Renaming or moving Oracle files (oracle-base.com).

Schema and table sizes

SELECT sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments
WHERE owner = UPPER('&schema_name');

select owner,sum(bytes)/1024/1024/1024 as "Size in GB"
from dba_segments group by owner;

Top 10 biggest tables in the whole database:

set linesize 300;
set pagesize 300;
column owner format a10;
column table_name format a30;
column "SIZE (GB)" format 99999.99;
select *
from (
        select owner, segment_name table_name, bytes/1024/1024/1024 "SIZE (GB)"
        from dba_segments
        where segment_type = 'TABLE' and segment_name not like 'BIN%'
        order by 3 desc
    )
where rownum <= 10;

Top 10 biggest tables in one tablespace:

column owner format a10;
column table_name format a30;
column "SIZE (GB)" format 99999.99;

select *
from (
    select owner, segment_name table_name, bytes/1024/1024/1024 "SIZE (GB)"
    from dba_segments
    where segment_type = 'TABLE' and segment_name not like 'BIN%' and tablespace_name in ('USERS')
    order by 3 desc
    )
where rownum <= 10;

Size, location and row count of one table:

select owner, table_name, tablespace_name
from dba_tables
where table_name='MY_TABLE';

column segment_name format a20;
column "SIZE (GB)" format 99999.99;
Select
    segment_name,sum( bytes)/1024/1024/1024 as "SIZE (GB)"
from
    dba_segments
where
    segment_name not like 'BIN%'
    and segment_type = 'TABLE'
    and segment_name = 'MY_TABLE'
group by segment_name;

select count(*) from my_schema.MY_TABLE;

Block size, default tablespaces, directories

select value from v$parameter where name = 'db_block_size';
show parameter DB_BLOCK_SIZE

select username, account_status, default_tablespace, TEMPORARY_TABLESPACE from dba_users;

select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

Listing tablespaces and datafiles

SELECT TABLESPACE_NAME "TABLESPACE",status ,EXTENT_MANAGEMENT,FORCE_LOGGING,BLOCK_SIZE,SEGMENT_SPACE_MANAGEMENT
  FROM DBA_TABLESPACES;
SET PAGESIZE 60
SET LINESIZE 300

COLUMN Tablspace FORMAT A30
COLUMN "Filename" FORMAT A50
COLUMN "Size MB" FORMAT 999,999,999
COLUMN "Maximum Size MB" FORMAT 999,999,999

select
  TABLESPACE_NAME "Tablspace",
  FILE_NAME "Filename",
  BYTES/1024/1024 "Size MB",
  MAXBYTES/1024/1024 "Maximum Size MB",
  AUTOEXTENSIBLE "Autoextensible"
from
  SYS.DBA_DATA_FILES;

Tablespace properties (extent management, allocation type, bigfile, files, size, used):

select
    a.TABLESPACE_NAME,
    a.CONTENTS,
    a.EXTENT_MANAGEMENT,
    a.ALLOCATION_TYPE,
    a.SEGMENT_SPACE_MANAGEMENT,
    a.BIGFILE,
    a.STATUS,
    nvl(sum(b.count_files),0) FILES,
    nvl(sum(b.bytes),0) "SIZE",
    nvl(sum(b.maxbytes),0) MAX_SIZE,
    nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0) "USED"
from DBA_TABLESPACES a,
    (
    select TABLESPACE_NAME,
        sum(BYTES) bytes,
        count(*) count_files,
        sum(greatest(MAXBYTES,BYTES)) maxbytes
    from DBA_DATA_FILES
    group by TABLESPACE_NAME
    union all
    select TABLESPACE_NAME,
        sum(BYTES),
        count(*),
        sum(greatest(MAXBYTES,BYTES)) maxbytes
    from DBA_TEMP_FILES
    group by TABLESPACE_NAME
    ) b,
    (
    select TABLESPACE_NAME,
        sum(BYTES) free_bytes
    from DBA_FREE_SPACE
    group by TABLESPACE_NAME
    union all
    select TABLESPACE_NAME,
        sum(BYTES_FREE) free_bytes
    from V$TEMP_SPACE_HEADER
    group by TABLESPACE_NAME
    ) c
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
    and a.TABLESPACE_NAME = c.TABLESPACE_NAME (+)
group by
    a.TABLESPACE_NAME,
    a.CONTENTS,
    a.EXTENT_MANAGEMENT,
    a.ALLOCATION_TYPE,
    a.SEGMENT_SPACE_MANAGEMENT,
    a.BIGFILE,
    a.STATUS
order by a.TABLESPACE_NAME;

Extent settings per tablespace:

col max_extents format a4 head 'Max|Ext'
col pct_increase format 999 head 'Pct|Inc'
col extent_management format a10 head 'Extent|Management'
col allocation_type format a10 head 'Allocation|Type'
col status format a7 head 'Status'
select   tbs.tablespace_name
,           tbs.initial_extent
,           tbs.next_extent
,           decode(tbs.max_extents,2147483645,'UL',tbs.max_extents) max_extents
,           tbs.pct_increase
,           tbs.extent_management
,           tbs.allocation_type
,           tbs.status
from    dba_tablespaces tbs
order by 1
/

Temporary tablespaces and tempfiles:

SELECT TABLESPACE_NAME FROM dba_tablespaces WHERE contents = 'TEMPORARY';
SELECT Name FROM v$tempfile;

select FILE_ID, FILE_NAME, STATUS, TABLESPACE_NAME
from DBA_TEMP_FILES
order by FILE_ID;

SYSAUX occupants:

select occupant_name, space_usage_kbytes from v$sysaux_occupants;

Tablespace usage and free space

Allocated, used and free space per tablespace, with percentages (see also How to resolve ORA-01653):

SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"
FROM   (SELECT tablespace_name,round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
       (SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name FROM dba_segments GROUP BY tablespace_name) tu
WHERE  df.tablespace_name = tu.tablespace_name;

Usage with autoextend taken into account (maximum size from maxbytes):

set linesize 1000;

with  usp as ( select
                  tablespace_name,count(*) nb_datafile
                , sum(bytes)/1024/1024 tot_file_mb
                , sum(greatest(bytes, maxbytes))/1024/1024 max_mb
             from
                 dba_data_files
            group by tablespace_name),
            fs as  (
                    select tablespace_name,sum(bytes)/1024/1024 as freespace_mb
                    from dba_free_space
                group by tablespace_name
                    )
select
      usp.tablespace_name, Freespace_mb,max_mb,NB_datafile,round(((tot_file_mb/usp.max_mb)* 100),2)
from
        usp inner join fs on usp.tablespace_name = fs.tablespace_name;

Usage from the built-in metrics view (blocks converted to MB with the block size):

column value new_value dbblock noprint
select value from v$parameter where name = 'db_block_size';
select tablespace_name,
used_space used_blocks,
(used_space*&dbblocks)/(1024*1024) used_mb,
tablespace_size tablespace_blocks,
(tablespace_size*&dbblocks)/(1024*1024) tablespace_mb,
used_percent
from dba_tablespace_usage_metrics;

Free space, largest free chunk and number of free chunks in one tablespace:

set feedback off
set echo off
set numwidth 15
set linesize 150
Accept tname Prompt "Enter Tablespace Name : "
Select (Sum(bytes)/1024/1024) Free_space_MB,(max(bytes)/1024/1024) Largest_Free_chunck_MB,count(*) No_of_free_chunk
from dba_free_space where tablespace_name=upper('&tname');

Allocated size of one tablespace:

Select (sum(bytes)/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');

High-water mark of each datafile

Useful to know how far a datafile can be shrunk:

column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999
select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name;

Tablespace growth history

Based on AWR history (requires the Diagnostics Pack license):

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;

Maximum datafile size

Block size is returned by:

select distinct bytes/blocks from user_segments;

A smallfile datafile can hold at most 4194303 blocks:

Block size Maximum datafile size
2k 8 GB
4k 16 GB
8k 32 GB
16k 64 GB
32k 128 GB

A BIGFILE tablespace (since Oracle 10g) has a single datafile that can hold up to 4294967295 blocks:

Block size Maximum datafile size
2k 8 TB
4k 16 TB
8k 32 TB
16k 64 TB
32k 128 TB

See How big can a datafile be.

Creating tablespaces and adding datafiles

create tablespace app_tabspace
   datafile '/u01/oradata/ORCL/app_tabspace01.dbf'
    size 10M autoextend on;

ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/ORCL/users_02.dbf'
size 100m;

ALTER TABLESPACE APP_DATA ADD DATAFILE '/u01/oradata/ORCL/app_data02.dbf' SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 32767M;

For an 8k block size, the 32 GB limit is 33554432 KB; the original note uses MAXSIZE 33554416K (32 GB minus 16 KB) as a safe maximum:

ALTER TABLESPACE "APP_INDEX"
    ADD DATAFILE '/u01/oradata/ORCL/app_index03.dbf'
    SIZE 100M
    AUTOEXTEND ON NEXT 100M MAXSIZE 33554416K;

Temporary tablespace:

create temporary tablespace app_tabspace_temp
tempfile '/u01/oradata/ORCL/app_tabspace_temp.dbf'
size 5M autoextend on;

Resizing and autoextend

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 40000M;

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND ON NEXT 30K MAXSIZE 1000K;

Changing tablespace status, renaming

alter tablespace ica offline;
alter tablespace ica online;

alter tablespace ica read only;
alter tablespace ica read write;

alter tablespace Tbs1 rename to Tbs2;

Default storage parameters (dictionary-managed tablespaces):

ALTER TABLESPACE SYSTEM
DEFAULT STORAGE ( INITIAL 100K NEXT 100K MINEXTENTS 1 MAXEXTENTS 300 PCTINCREASE 1);

Dropping a tablespace or a datafile

Check first that nothing is stored in the tablespace:

select * from dba_segments
where tablespace_name = '<tablespace_to_drop>';
drop tablespace app_tabspace;
drop tablespace <tablespace_name> including CONTENTS AND datafiles;

-- tablespace whose tables are referenced by foreign keys from other tablespaces
DROP TABLESPACE tbs3
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

-- remove a non-empty tablespace (datafiles are kept on disk)
DROP TABLESPACE APP_OLD INCLUDING CONTENTS;

Drop a single datafile from a multi-file tablespace (reference):

create tablespace x datafile '/tmp/x1.dbf' size 4m, '/tmp/x2.dbf' size 4m;
alter tablespace x drop datafile '/tmp/x2.dbf';

More details: Oracle DROP TABLESPACE.

Where are my objects stored?

select tablespace_name from all_tables where owner = 'MY_SCHEMA' and table_name = 'MY_TABLE';
select table_name, tablespace_name from all_tables where owner = 'MY_SCHEMA';
select tablespace_name from all_indexes where owner = 'MY_SCHEMA' and index_name = 'MY_INDEX';
select index_name, tablespace_name from all_indexes where owner = 'MY_SCHEMA';

Other file types

Archived logs:

select NAME,
    THREAD#,
    SEQUENCE#,
    BLOCKS*BLOCK_SIZE "SIZE",
    RESETLOGS_ID,
    COMPLETION_TIME,
    STATUS
from V$ARCHIVED_LOG
order by SEQUENCE#;

Control files:

select NAME, STATUS
from V$CONTROLFILE
order by NAME;

Redo log files:

select l.GROUP#,
    l.THREAD#,
    l.SEQUENCE#,
    l.BYTES,
    l.MEMBERS,
    l.STATUS,
    f.TYPE,
    f.MEMBER
from V$LOGFILE f, V$LOG l
where l.GROUP# = f.GROUP#
order by GROUP#,MEMBER;

Reducing a tablespace

To reclaim space, check the high-water mark of each datafile (see above), then resize the datafile below its current size but not under the high-water mark. See How to shrink an Oracle tablespace.

Notes

Mastodon LLIMastodon SocialFosstodon