Oracle | Data & Temp Files

Oracle Data & Temp Files Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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	TS$
CDB_FREE_SPACE	DBA_HIST_DATAFILE	V$DATAFILE
CDB_HIST_DATAFILE	DBA_HIST_TEMPFILE	V$DATAFILE_HEADER
CDB_HIST_TEMPFILE	DBA_TEMP_FILES	V$DBFILE
CDB_TEMP_FILES	DBMS_SPACE	V$TEMPFILE
DBA_DATA_FILES	FILE$	X$KTFBHC

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;

Makes possible increasing or decreasing 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 
      120G;

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 256G;

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 256G;

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 DATABASE MOVE DATAFILE '/u02/app/oracle/oradata/orabase/uwdata01.dbf' 
      TO '+DATA';

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 10G;

      

      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/1024 GB, blocks FROM dba_data_files UNION ALL SELECT file_name, tablespace_name, bytes/1024/1024/1024 GB, 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; -- 4 tables are created in the tablespace. -- Observe which data file contains the first segments created SELECT segment_name, file_id FROM dba_extents WHERE tablespace_name = 'BOWIE_DATA'; -- first extent are created in the first data file created -- allocate an additional extent 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; -- the secondextent is created in the second data file -- 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. -- the first extent is allocated to the first data file (if it has sufficient 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". */