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
- Dropping a tablespace with
INCLUDING CONTENTS AND DATAFILESis irreversible: checkdba_segmentsand have a backup first. - The growth history query reads AWR views, which require the Diagnostics Pack license.
- Paths, tablespace and schema names above are generic examples; adapt them to your environment.