General Information |
Dependencies | file$ | ts$ | x$ktfbhc | dba_data_files | dba_temp_files | | dba_free_space | gv$dbfile | |
|
| |
Alter Data Files |
Resize An Existing Datafile
| ALTER DATABASE DATAFILE '<data_file_name | data_file_number>'
RESIZE <n> K|M|G|T|P|E;
Beware that you can only decrease the size of the datafile with the space that is free between highest used block of the datafile and the last block of the file. If the tablespace is fragmented, the free spaces between extents cannot be deallocated this way. Check dba_free_space for details. |
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase ools02.tom'
RESIZE 50M;
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase ools03.dan'
RESIZE 50M; |
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 'c:\oracle\oradata\orabase ools02.tom' SIZE 20M; |
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 1G
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 | <n>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 Temp Files |
Resize | alter database tempfile <temp file name>
resize <integer> K|M|G|T|P|E; |
ALTER
DATABASE TEMPFILE 'temp01.dbf' RESIZE 100M; |
Drop | alter database tempfile <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; |
Data File Block Sizing
| -- as root created a file system with block size 1024
mkfs.ext3 -b 1024 /dev/sda3
-- mounted it
mount /dev/sda3 /mnt/test
-- and issued
iostat -d -t -x /dev/sda3
-- in another shell
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 write were in blocks of 1K. |
Uneven Datafile Usage Within A Tablespace
| CREATE
TABLESPACE bowie_data
DATAFILE 'c:\bowie\bowie_data01.dbf' size 10m,
'c:\bowie\bowie_data02.dbf' size 10m,
'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". |
