Oracle Data & Temp Files
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
This Library page is dedicated to syntax related to the statement ALTER DATABASE DATAFILE, ALTER DATABASE TEMPFILE, and CREATE DATAFILE.

Please note that file sizes in these demos are based on the likelihood that someone trying this in a classroom environment will have very little disk space available. These days it makes no sense to allocate space like these dmeos do for anything other than a personal education. Similarly some calculation examples have been simplified to make the math easier to follow.

My general rule, when people with commercial environments complain that they don't have a free TB, is to tell them to buy more disk. Simply put a TB is nothing these days. If your management can't afford 1TB you need to update your resume and find another place to work because your management is likely both clueless and abusive. If the company is going to fall over if it buys sufficient spinning disk it is such a risky enterprise you should bail out at the first possible opportunity.
Data Dictionary Objects
CDB_DATA_FILES DBA_FREE_SPACE V$DATAFILE
CDB_FREE_SPACE DBA_HIST_DATAFILE V$DATAFILE_HEADER
CDB_HIST_DATAFILE DBA_HIST_TEMPFILE V$DBFILE
CDB_HIST_TEMPFILE DBA_TEMP_FILES V$TEMPFILE
CDB_TEMP_FILES FILE$ X$KTFBHC
DBA_DATA_FILES TS$  
 
Alter Datafile Clause
Bring an offline datafile online or take an online datafile offline ALTER DATABASE DATAFILE <'file_path_and_name' | file_number> <ONLINE | OFFLINE [FOR DROP]>;
SELECT file_name, file_id, online_status
FROM dba_data_files;

ALTER DATABASE DATAFILE
'u06/oradata/tools01.dbf' OFFLINE;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' ONLINE;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' OFFLINE FOR DROP;
Valid only for bigfile tablespaces lets you increase or decrease the size
of a single datafile to an absolute size.

Datafile size can only be decreased with space that is free between  the highest used block and the last file block. If fragmented, the free spaces between extents cannot be deallocated
ALTER DATABASE DATAFILE <'file_path_and_name' | file_number> RESIZE <integer><K | M | G | T | P | E>;
SELECT file_name, file_id, (bytes/1024/1024) SIZE_MB
FROM dba_data_files;

ALTER DATABASE DATAFILE '/app/oracle/oradata/orabase\users01.dbf' RESIZE 150M;
Make a datafile Autoextend ALTER DATABASE DATAFILE <'file_path_and_name' | file_number>
AUTOEXTEND <OFF | ON [NEXT SIZE <integer><K | M | G | T | P | E>
MAXSIZE <UNLIMITED | <integer><K | M | G | T | P | E>;
SELECT file_name, file_id, autoextensible
FROM dba_data_files;

ALTER DATABASE DATAFILE
'/app/oracle/oradata/orabase\users01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE
'/app/oracle/oradata/orabase\users01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;
Specify END BACKUP to take out of online backup mode any data files in the database currently in online backup mode. The database must be mounted (either open or closed) when you perform this operation. ALTER DATABASE DATAFILE <'file_path_and_name' | file_number> END BACKUP;
SELECT file_name, file_id, tablespace_name
FROM dba_data_files;

ALTER DATABASE DATAFILE '/app/oracle/oradata/orabase\users01.dbf' END BACKUP;
 
Alter Tempfile Clause
Add ALTER TABLESPACE ADD TEMPFILE <temp_file_name>;
ALTER TABLESPACE temp ADD TEMPFILE 'temp01.dbf';
Resize ALTER TABLESPACE TEMPFILE <temp_file_name> RESIZE <integer><K | M | G | T | P | E>;
ALTER DATABASE TEMPFILE 'temp01.dbf' RESIZE 2G;
Drop ALTER DATABASE TEMPFILEe <temp_file_name> DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 'temp01.dbf' DROP INCLUDING DATAFILES;
 
Create Datafile Clause
Add A Datafile To An Existing Tablespace ALTER DATABASE CREATE DATAFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T | P | E> AS '<tablespace_name>';
conn / as sysdba

alter session set container = 'PDBDEV';

ALTER DATABASE CREATE DATAFILE 'c:\oracle\oradata\orabase\uwdata03.dbf' SIZE 100M
AS 'UWDATA';
Add A Datafile To An Existing Tablespace with ALTER TABLESPACE ALTER TABLESPACE <tablespace_name>
ADD DATAFILE '<path_and_file_name>' SIZE <n>K | M | G | T | P | E;
conn / as sysdba

alter session set container = 'PDBDEV';

ALTER TABLESPACE tools ADD DATAFILE '/u03/oradata/orabase/tools02.tom' SIZE 1G;
 
Move Datafile Clause
Relocate A Datafile ALTER DATABASE MOVE DATAFILE <['path_and_file_name' | 'ASM_file_name' | file_number]>
TO <['path_and_file_name' | 'ASM_file_name'> [REUSE] [KEEP];
conn / as sysdba

alter session set container = 'PDBDEV';

ALTER DATABASE MOVE DATAFILE '/app/oracle/oradata/orabase/uwdata03.dbf'
TO '/app/oracle/oradata/orabase/uwdata03.dbf' REUSE;
 
Drop Data File
Drop A Datafile ALTER DATABASE DATAFILE '<file_name_or_file_number>' [offline] DROP;
set linesize 121
col file_name format a80

SELECT file_name, SUM(bytes)/1024/1024 DF_SIZE
FROM dba_data_files
GROUP BY file_name;

ALTER TABLESPACE users ADD datafile SIZE 100M;

SELECT file_name, SUM(bytes)/1024/1024 DF_SIZE
FROM dba_data_files
GROUP BY file_name;

ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\UKOUG\DATAFILE\O1_MF_USERS_35HCKNFO_.DBF'
OFFLINE DROP;

or

ALTER TABLESPACE users DROP DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\UKOUG\DATAFILE\O1_MF_USERS_35HCKNFO_.DBF';
 
Rename File Clause
Move Tablespace Datafile

Can also be used to move SYSTEM, SYSAUX, and TEMP tablespace files
ALTER DATABASE RENAME FILE '<current_file_name>' TO '<new_file_name>'
conn sys@pdbdev as sysdba

SHUTDOWN IMMEDIATE; -- in a PDB this moves the PDB to mount state

host

$ cp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbf

$ exit

-- copy the datafile to it's new location
ALTER DATABASE RENAME FILE '/u01/oradata/tools01.dbf' TO '/u06/oradata/tools01.dbf';

-- then

ALTER DATABASE OPEN;

-- then you can safely delete the old datafile
host

$ rm /u01/oradata/tools.01.dbf

$ exit
 
Data File Related Queries
Data File Information set linesize 121
col file_name format a45
col tablespace_name format a20

SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks
FROM dba_data_files
UNION ALL
SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks
FROM dba_temp_files
ORDER BY tablespace_name, file_name;
High Water Mark Calculation col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (
  SELECT /*+ RULE */ ddf.tablespace_name,
    REPLACE(ddf.file_name, 'C:\ORACLE\PRODUCT','$ORACLE_HOME') file_name,
    ddf.bytes/1048576 file_size,
    (ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
  FROM dba_data_files ddf,
    (SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,
    dba_extents de,
    (SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
  WHERE ddf.file_id = ebf.file_id
  AND de.file_id = ebf.file_id
  AND de.block_id = ebf.maximum
  ORDER BY 1,2);
Data File Block Sizing -- as root create a file system with block size 1024bytes (1K)
mkfs.ext3 -b 1024 /dev/sda3

-- mount it
mount /dev/sda3 /mnt/test

-- and in a different shell issue
iostat -d -t -x /dev/sda3

dd if=/tmp/foo of=/mnt/test/foo2 bs=1024k

-- the results
Time: 08:47:05
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz
avgqu-sz await svctm %util
/dev/sda3 0.00 0.00 2.00 0.00 4.00 0.00 2.00 0.00
2.00
0.10 50.00 50.00 1.00

Time: 08:47:10
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
avgrq-sz
avgqu-sz await svctm %util
/dev/sda3 0.00 10200.40 0.20 83.60 0.40 20568.00 0.20
10284.00 245.
45 67.92 810.50 31.03 26.00

-- do the math
wsec/s / wrqm/s = 20568.00 / 10200.40 = 2,017

-- roughly two sectors of 512 bytes. So writes were in blocks of 1K
Uneven Datafile Usage Within A Tablespace CREATE TABLESPACE bowie_data
DATAFILE 'c:\bowie\bowie_data01.dbf' size 100M,
'c:\bowie\bowie_data02.dbf' size 100M,
'c:\bowie\bowie_data03.dbf' size 10M
UNIFORM SIZE 64;

col segment_name format a30

SELECT file_id, file_name
FROM dba_data_files
WHERE tablespace_name = 'BOWIE_DATA';

CREATE TABLE one (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE two (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE three (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE four (x NUMBER) TABLESPACE bowie_data;

-- now we've create 4 tables in this tablespace. Let's see which data file they were placed in ...

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA';

-- note that *all* tables have their first extent created in the *first* data file defined to the tablespace

-- now lets grow these tables and see what happens next

ALTER TABLE one ALLOCATE EXTENT;
ALTER TABLE two ALLOCATE EXTENT;
ALTER TABLE three ALLOCATE EXTENT;
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

... and the second extent of each table has been created in the second data file of the tablespace

If a particular table were to keep growing ...

ALTER TABLE four ALLOCATE EXTENT;
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

/* see how the extents get allocated to the data files in a round robin fashion. But the first extent is allocate to the first data file (providing it has sufficent space) ... */

CREATE TABLE five (x NUMBER) TABLESPACE bowie_data;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

-- let's add a new data file. What happens now ...

ALTER TABLESPACE bowie_data
ADD DATAFILE 'c:\bowie\bowie_data04.dbf' SIZE 100M;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

-- a new extent is added to table four. And uses the new datafile
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

/* and now the new file is used. The files are still used in a round robin fashion with the new file slipping in.

Note how file 16 is the *most* used file and file 19 is the least. If I were to allocate several new tables that were only 1 or 2 extents in size, see how file 16 would be the one to be most "filled". */

Related Topics
ASM
Data Dictionary
DBMS_SPACE
DBMS_SPACE_ADMIN
SecureFiles
Tablespaces

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved