Translate this Page


Sometimes at the first installation, we don’t care about the size of UNDO Tablespace. We just create it with maximum extensible unlimited size. So there’s one big data file with a big I/O since write-read process only on that file. This article I wrote for give you a solution how to replace an UNDO Tablespace with a big datafile with a new UNDO Tablespace with several tiny datafiles.

  1. Check the UNDO setting in the database :
  2. SQL> show parameter undo
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- --------------------
    undo_management                      string      AUTO
    undo_retention                       integer     10800
    undo_tablespace                      string      UNDOTBS1

    Continue reading »


There are 2 methods to move the data files of the tablespace to other location.

1 . When Database is Up (for some tablespace or datafile)

To move or rename the Data File  when database is up we should take the related tablespaces (eg. TESTD) offline, at this status we can’t do DML or SELECT to all objects that stored in that tablespace.

ATTENTION: You can use this way if the datafile that need to be changed or moved is not belong to SYSTEM tablespaces, and not contain active rollback segments or temporary segments.

  • Take the Tablespace Offline
    • [oracle@levicorp ~] sqlplus ‘/as sysdba’
    • SQL > ALTER TABLEPSPACE testd OFFLINE; Continue reading »

Sometime we need to move Temporary Files to other location. This steps will guide you how to do that easily and safety ;)

1. Login as SYSDBA
2. Check the Tempfile status

SQL> SELECT v.file#, t.file_name, v.status
 2  FROM dba_temp_files t, v$tempfile v
 3  WHERE t.file_id = v.file#;

 FILE#     FILE_NAME                 STATUS
---------- ------------------------- -------
 1         /u01/temp01.dbf           ONLINE
 2         /u02/temp02.dbf           ONLINE
 3         /u02/temp03.dbf           ONLINE

3. Make OFFLINE the tempfile that need to move Continue reading »

© 2012 LevICORP's webnote Suffusion theme by Sayontan Sinha