Oracle DataFiles

Oracle DataFiles

Version 9.2

General InformationData Dictionary Objects Related To Data Filesfile $ DBA_DATA_FILESDBA_FREE_SPACENOTES:

x

x

x Create Data Files Alter Data FilesResize An Existing Tablespace DatafileALTER DATABASE DATAFILE '' RESIZE K | M; 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 can not be deallocated this way Check dba_free_space for details.ALTER DATABASE DATAFILE 'c: /oracle/oradata/orabase/tools02.tom' RESIZE 50M;. ALTER DATABASE DATAFILE 'c: /oracle/oradata/orabase/tools03.dan' RESIZE 50M; Add A Datafile To An Existing TablespaceALTER TABLESPACE ADD DATAFILE '' SIZE K | M; ALTER TABLESPACE toolsADD DATAFILE 'c: /oracle/oradata/orabase/tools02.tom' SIZE 20M; Move Tablespace DatafileSHUTDOWNSTARTUP MOUNTCopy the datafile to it's new locationALTER DATABASE RENAME FILE '' tO ''; thenALTER DATABASE OPEN; After that, you Can Safely Delete T he old datafile.SQL> CONN / AS SYSDBASQL> SHUTDOWNSQL> STARTUP MOUNTSQL> HOST $ cp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbf$ EXITSQL> ALTER DATABASE RENAME FILE '/ u01 / oradata / tools01. DBF 'to' /u06/oradata/tools01.dbf';sql> ALTER DATABASE OPEN; SQL> Host $ RM /U01/Ordata/tools.01.dbf (Exitmove System TableSpace DataFilethe Data Dictionary Can Never Be Offline, with the rest of the database operational. Moving the SYSTEM tablespace's datafiles requires a different technique because 'alterdatabase' generally means you are modifying the controlfile.All 'RENAME datafile' x '

to 'y' command does is to reset the pointers to the datafiles stored in the Control File which SMON reads at startup) conn / AS SYSDBASHUTDOWNcopy the datafile to its new location with operating system commands.STARTUP NOMOUNTALTER DATABASE MOUNT; ALTER DATABASE RENAME FILE ' c: /oracle/oradata/system01.dbf 'TO' d: /oracle/oradata/system01.dbf '; ALTER DATABASE OPEN; Alter Temp FilesResizealter database tempfile resize M; ALTER DATABASE TEMPFILE' temp01 .dbf 'RESIZE 100M; Dropalter database tempfile drop including datafiles; ALTER DATABASE TEMPFILE' temp01.dbf 'dROP INCLUDING DATAFILES; drop Data FileDrop A Datafile: Works only on tablespaces consisting of a single datafileALTER DATABASE DATAFILE' '[offline] DROP; ALTER DATABASE DATAFILE' C: /ORACLE/ORADATA/ORABASE/TOOLS02.TOM'OFFLINE DROP; Data File Related Queries SELECT file_name, tablespace_name, bytes / 1024/1024 MB, blocksFROM dba_data_filesUNION ALLSELECT file_name, tablespace_name , Bytes / 1024/1024 MB, blocksFROM dba_temp_filesORDER BY tablespace_name, file_name; Uneven Datafile Usage Within A TablespaceCREATE TABLESPACE bowie_dataDATAFILE 'c: /bowie/bowie_data01.dbf' size 10m, 'c: /bowie/bowie_data02.dbf' size 10m, ' c: /bowie/bowie_data03.dbf 'size 10muniform size 64; col segment_name format a30SELECT file_id, file_nameFROM dba_data_filesWHERE 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_extentsWHERE 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_idFROM dba_extentsWHERE 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_idFROM dba_extentsWHERE 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_idFROM dba_extentsWHERE 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_idFROM dba_extentsWHERE 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_IDFROM DBA_EXTENTSWHERE TABLESPACE_NAME = 'Bowie_Data'

ORDER BY segment_name; ALTER TABLE four ALLOCATE EXTENT; SELECT segment_name, file_idFROM dba_extentsWHERE 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 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 ".elated Topics