Chapter 9 – Managing Datafiles and Tempfiles
Posted by Kamran Agayev A. on December 1st, 2009
– Oracle database assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view. Relative file number uniquely identifieds a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number.
– When starting an instance, the DB_FILES parameter specifies the maximum number of database files that can be opened for this database. Default value is 200
– If several disk drives are available to store the databsae, consider placing potentially contending datafiles on separate disks. This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time
– Datafiles should not be stored on the same disk drive that stores the databsae redo log files. if the datafiles and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures.
– To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column
– To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE FOR DROP clauses
– Clauses of the ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace.
ALTER TABLESPACE tbs_test DATAFILE [ONLINE | OFFLINE]
To rename and relocate datafiles use the following steps:
· Take the tablespace that contains the datafiles offline:
ALTER TABLESAPCE users OFFLINE NORMAL;
· Rename the datafiles using the operating system
· Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database
ALTER TABLESPACE users RENAME DATAFILE ‘file1’ TO ‘file2’;
· Backup the database
– To drop the datafile, the database must be open and the file must be empty. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile. You can’t drop the first or only datafile in a tablespace, or datafiles in a read only tablespace or SYSTEM tablespace.
– If you want to configure the database to use checksums to verify data blocks, set the initialization parameter DB_BLOCK_CHECKSUM to TRUE. This causes the DBWn process and the direct loader to calculate a checksum for each block and to store the checksum in the block header when writing the block to disk.
The checksum is verified when the block is read, but only if DB_BLOCK_CHECKSUM is TRUE and the last write of the block stored a checksum. If corruption is detected, the database returns message ORA-01578 and writes information about the corruption to the alert log
March 28th, 2010 at 5:58 pm
Hi Kamran,
How are you?
I would like to know some good material to learn for the beginners in Oracle DBA.
I am a beginner in this field having trying to gain knowledge on oracle dba.
Recently i have completed my oracle dba 11g course from one of a institute and try to do lot of practice on it.
i wanted to know how actual the dba task is in live project in real time.
If you let me know some good material or any good exercise by which i can go step by step to improve in my dba field.
Please let me know.
Best regards,
Aijaz Khan
March 28th, 2010 at 6:39 pm
Here i would like to ask you somethg on undo tablespace.
SYS>> Sho parameter undo
here what i want to know is how i will receive an error ora 01555 in this example i tried to do it many times but i am not receiving any error. I am working on oracle 11g on redhat linux 4 on VMware.
Which parameter i should mention first on paramter file i.e. init.ora for testing undo tablespace.
undo_management=auto
undo_tablespace=undotbs
SYS>> sho Parameter undo
sys>> select tablespace_name, contents, retention from dba_tablespace;
by default undo tablespace is Noguarantee.
Here i am creating one small undo tablespace by name Undo1
sys>> create undo tablespace undo1 datafile ‘/disk2/oradata/suman/undo1.dbf’ size 4m;
sys>> sho parameter undo
by default it is undotbs
Now i want to active the recent undo1 tablespace from undotbs to undo1
sys>> alter system set undo_tablespace=undo1;
sys>> sho parameter undo
now the active undo tablespace is undo1
now i am connecting to a user by name user1
user1>>
here i am creating some sample tables.
user1>> @$ORACLE_HOME/Sqlplus/demo/demobld
user1>> select * from emp;
14 records;
user1>> insert into emp select * from emp;
now i create here a loop in order to update this statement continuously in order to fill the undo1 tablespace.
begin
loop
update emp set sal=2000 where empno=7566;
commit;
end loop;
end;
now here to the another session and try to retrieve the data.
sys>> select * from u1.emp;
if somebody wants to do the transaction since the session is very big and overwritten the earlier one then it raises an error ORA-01555.
i wanted to know how to do it step by step i am doing the same steps but not receiving the exact error can you plz help me out in this situation how to face this problem in real time experience.
Best regards,
Aijaz Khan