Oracle Data & Temp Files

Oracle Data & Temp Files Version 11.2.0.3

General Information

Note: Please not 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 sense to allocate space like this. Similarly some calculation examples have been simplified to make the math easier to follow.

Data Dictionary Objects

DBA_DATA_FILES	FILE$	TS$
DBA_FREE_SPACE	GV$DBFILE	X$KTFBHC
DBA_TEMP_FILES

Alter Data Files

Resize An Existing Datafile

Datafile size can only be decreased with space that is free between the highest used block oand the last file block. If fragmented, the free spaces between extents cannot be deallocated

ALTER DATABASE DATAFILE '<data_file_name | data_file_number>' RESIZE <integer> K|M|G|T|P|E;

ALTER DATABASE DATAFILE '/u03/oradata/orabase/tools02.tom'
RESIZE 50G;

ALTER DATABASE DATAFILE '/04//oradata/orabase/tools03.dan'
RESIZE 50G;

Add A Datafile To An Existing Tablespace

ALTER TABLESPACE <tablespace_name>
ADD DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E;

ALTER TABLESPACE tools ADD DATAFILE '/u03/oradata/orabase/tools02.tom' SIZE 2G;

Alternative Add A Datafile To An Existing Tablespace syntax

ALTER DATABASE CREATE DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E
AS '<tablespace_name>';

ALTER DATABASE CREATE DATAFILE 'c:\oracle\oradata\orabase\uwdata03.dbf' SIZE 10G
AS 'UWDATA';

Move Tablespace Datafile

Can also be used to move SYSTEM, SYSAUX, and TEMP tablespace files

SHUTDOWN

STARTUP MOUNT

-- copy the datafile to it's new location

ALTER DATABASE RENAME FILE '<old_full_path>' TO '<new_full_path>';

-- then

ALTER DATABASE OPEN;

-- then you can safely delete the old datafile

conn / as sysdba

shutdown immediate;

startup mount

host

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

$ exit

alter database rename file '/u01/oradata/tools01.dbf'
to '/u06/oradata/tools01.dbf';

alter database open

host

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

$ exit

Autoextend

ALTER DATABASE DATAFILE <'data_file_name' | data_file_number>
AUTOEXTEND <OFF | ON [NEXT SIZE <n>K|M|G|T|P|E
MAXSIZE <UNLIMITED | <integer>K|M|G|T|P|E>;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

Online / Offline

ALTER DATABASE DATAFILE <'data_file_name' | data_file_number>
<ONLINE | OFFLINE [FOR DROP]>;

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;

Alter for Temp Files

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

Drop

ALTER DATABASE TEMPFILEe <temp_file_name> DROP INCLUDING DATAFILES;

ALTER DATABASE TEMPFILE 'temp01.dbf' DROP INCLUDING DATAFILES;

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 50M;

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';

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;

/* You can 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 10M;

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".