oracle-databaseoracle11goraclecommand

How to perform undo operation using Oracle Basic Command


I am new with Oracle Database and I want to know some command relate with Oracle. Does anyone help me with below questions:

1. Which command use to show undo_retention period?
2. Which command use to show undo tablespace name?
3. how to disable/enable undo Retention guarantee?

Thanks,


Solution

    1. Which command use to show undo_retention period?
    2. Which command use to show undo tablespace name?

    Open SQL*PLUS as SYSDBA and use show parameter command as shown below.

    SQL> show parameter undo
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management                      string      AUTO
    undo_retention                       integer     2400
    undo_tablespace                      string      UNDOTBS1
    
    1. how to disable/enable undo Retention guarantee?

    Now you have the name of the undo tablespace.

    Use the following command to switch to undo retention guarantee.

    ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE; 
    

    To switch back to noguarantee.

    ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE; 
    

    Use following query to check whether your undo tablespace has retention guaranteed or not.

    SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name = 'UNDOTBS1';
    

    For more details: Managing Undo