Skip to content

Oracle System State Dump

    Using Oracle System State Dump. When the database encounters a serious performance problem or hang, sqlplus cannot connect to the server. In this case, if you want to obtain the current status of the database for later diagnosis, then we need to use systemstate dump to know what the process is doing, what it is waiting for, who is the resource owner, and who is blocking others. In case of the above problems, it is very helpful to analyze the cause of the problem by collecting systemstate dump in time. 

    There are two ways to connect to sqlplus using a preliminary connection.

    sqlplus -prelim / as sysdba
    sqlplus /nolog
    set _prelim on
    connect / as sysdba

    To generate it:

    oradebug setmypid;
    oradebug unlimit;
    oradebug dump systemstate 266
    oradebug tracefile_name

    For all cluster:

    oradebug setmypid;
    oradebug unlimit;
    oradebug -g all dump systemstate 266
    oradebug tracefile_name

    An example of execution:

    SQL> oradebug setmypid;
    Statement processed.
    SQL> oradebug unlimit;
    Statement processed.
    SQL> oradebug dump systemstate 266
    Statement processed.
    SQL> oradebug tracefile_name
    /db/oracle/diag/rdbms/grepora/GREPORA/trace/GREPORA_ora_18256.trc

    Also See:

    ORAchk