Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Database Utilities

    Oracle Server (Util O/1)

    The Oracle Server Utilty menu allows you to manage the Oracle server.
    To access the Oracle Server menu:
    1. Log on to a Rosetta server with the dps user.
      If you are using a dedicated Oracle server, you must log on to a server that has Rosetta installed in order to access Util O.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 1 to display the Oracle Server menu.
      O.1 Oracle Server
      ---------------
      0. Exit Procedure
      1. Activate Oracle Server
      2. Close Oracle Server
      3. Show Running Oracle Server
      4. Show Oracle Server Status
       

    Activate Oracle Server (Util O/1/1)

    In order for Rosetta to interact with Oracle, the Oracle server must be running. When Rosetta is installed on the same server as the Rosetta database, it does not require the Listener. However, the Listener must run on the server if a third-party product is used to connect Rosetta and the database or if there is a remote server connected to the database. For example, when Rosetta is installed on one server and the database is on a different server, the Listener must be running on the database server in order for Rosetta to operate correctly.
    Rosetta and Oracle may start automatically at boot time (if this option is set during installation).
    • This operation requires the EXL_DBA user name and password.
    • This utility will not be available if the Oracle database and the Rosetta application are installed on separate servers.
    To start up the Oracle server:
    1. Enter option 1 from the Oracle Server menu.
      The following prompt displays:
      To continue you will need to enter EXL_DBA username/password.
      Username/password: 
    2. Enter the EXL_DBA user name and password (EXL_DBA/EXL_DBA) to activate the Oracle server.
      The EXL_DBA user, which is created during installation, is provided with database administration privileges that allow you to start up or shut down the database.

    Close Oracle Server (Util O/1/2)

    This operation requires the EXL_DBA user name and password.
    This utility shuts down the Oracle server immediately by activating the Oracle shutdown immediate option. All the clients connected to the server are immediately logged out.
    To shut down the Oracle server:
    1. Enter option 2 from the Oracle Server menu. To access this menu, see Oracle Server (Util O/1).
      The following prompt displays:
      Do you want to restart Oracle server after closing? yes/[no]
    2. Enter yes to shut down and the restart the server automatically.
      The following prompt displays:
      To close Oracle server enter EXL_DBA username/password.
      username/password:
      To restart the server later, see Activate Oracle Server (Util O/1/1).
    3. Enter the EXL_DBA user name and password (EXL_DBA/EXL_DBA)to activate the Oracle server.
      The EXL_DBA user, which is created during installation, is provided with database administration privileges that allow you to start up or shut down the database.

    Show Oracle Server Status (Util O/1/4)

    This utility displays the status of the Oracle server.
    To display the status of the Oracle server, enter option 4 from the Oracle Server menu.
    The following is an example of the output of the Util O/1/4 Show Oracle Server Status utility:

    INSTANCE_N  HOST_NAME        VERSION       STARTUP_TI  STATUS      LOGINS
    ----------  ---------------  ------------  ----------  ----------  ----------
    dps         il-dtldev08c     12.1.0.2.0    16-SEP-18   OPEN        ALLOWED

    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    PL/SQL Release 12.1.0.2.0 - Production
    CORE    12.1.0.2.0      Production
    TNS for Linux: Version 12.1.0.2.0 - Production
    NLSRTL Version 12.1.0.2.0 - Production

    Oracle Listener (Util O/2)

    The Oracle Listener menu allows you to manage the Oracle Listener.
    O.2 Oracle Listener
    -----------------
    0. Exit Procedure
    1. Activate Oracle Listener
    2. Close Oracle Listener
    3. Show Running Oracle Listener
    4. Show Listener Status
    5. Show Listener Services
     
    This utility will not be available if the Oracle database and the Rosetta application are installed on separate servers.
    To access the Oracle Listener menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 2 to display the Oracle Listener menu.

    Activate Oracle Listener (Util O/2/1)

    This utility requires the Oracle software owner password.
    When a user process makes a connection request using a connect string, the Oracle Listener process examines the request and connects the user process to a server process. If Oracle and Rosetta are installed on the same server and no third-party products are used to connect to the database and no connections are made from a remote server, Rosetta can work without the Listener. In any other case, both the Oracle server and the Oracle Listener must be running. They may be started automatically at boot time (this is determined during installation) and also controlled by the Rosetta Oracle Management utilities.
    To start up the Oracle Listener:
    1. Enter option 1 from the Oracle Listener menu.
      The following prompt displays:
      To continue you will need to enter Oracle's password.
      Password:
    2. Enter the Oracle password to start up the Oracle Listener.

    Close Oracle Listener (Util O/2/2)

    This utility requires the Oracle software owner password.
    This utility shuts down the Oracle Listener immediately. You have an option to restart the listener automatically.
    To shut down the Oracle Listener:
    1. Enter option 2 from the Oracle Listener menu.
      The following prompt displays:
      Do you want to restart Oracle Listener after closing? yes/[no]
    2. Enter yes to shut down and then restart the Listener automatically.
      The following prompt displays:
      To restart Oracle Listener enter oracle's password.
      Password:
      To start the Listener later, see Activate Oracle Listener (Util O/2/1).
    3. Enter the Oracle password to restart the Oracle Listener.

    Show Listener Status (Util O/2/4)

    This utility displays the status of the Oracle Listener.
    To display the status of the Oracle Listener, enter option 4 from the Oracle Listener menu.
    The following example shows the results of this utility:

    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-OCT-2018 14:57:42

    Copyright (c) 1991, 2014, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(address=(protocol=ipc)(key=dps)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
    Start Date                16-SEP-2018 11:51:41
    Uptime                    36 days 3 hr. 6 min. 1 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /exlibris/app/oracle/product/12r1/network/admin/listener.ora
    Listener Log File         /exlibris/app/oracle/diag/tnslsnr/il-dtldev08c/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=dps)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=il-dtldev08c.corp.exlibrisgroup.com)(PORT=1521)))
    Services Summary...
    Service "dps" has 1 instance(s).
      Instance "dps", status UNKNOWN, has 1 handler(s) for this service...
    Service "dps.il-dtldev08c.corp.exlibrisgroup.com" has 1 instance(s).
      Instance "dps", status READY, has 1 handler(s) for this service...
    The command completed successfully

    Show Listener Services (Util O/2/5)

    This utility displays the Oracle Listener services. To display the Oracle Listener services, enter option 5 from the Oracle Listener menu.
    The following example shows the results of this utility:

    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-OCT-2018 14:58:41

    Copyright (c) 1991, 2014, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(address=(protocol=ipc)(key=dps)))
    Services Summary...
    Service "dps" has 1 instance(s).
      Instance "dps", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:3 refused:0
             LOCAL SERVER
    Service "dps.il-dtldev08c.corp.exlibrisgroup.com" has 1 instance(s).
      Instance "dps", status READY, has 1 handler(s) for this service...
        Handler(s):
          "DEDICATED" established:7882 refused:0 state:ready
             LOCAL SERVER
    The command completed successfully

    Oracle Logs (Util O/3)

    The Oracle Logs menu allows you to view the Oracle log file.
    O.3 Oracle Logs
    ---------------
    0. Exit Procedure
    1. View Oracle ALERT LOG
     
    To access the Oracle Logs menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 3 to display the Oracle Logs menu.

    NLS (Util O/6)

    The NLS menu allows you to view the NLS (National Language Support) parameters.
    O.6 NLS
    ---------------
    0. Exit Procedure
    1. Show NLS Parameters
     
    To access the NLS menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 6 to display the NLS menu.

    Show NLS Parameters (Util O/6/1)

    Rosetta uses the UTF8 character set. This utility shows the NLS (National Language Support) definition of the database.
    To view the NLS parameter settings:
    1. Enter option 1 from the NLS menu.
      For example, the utility displays the settings of the parameters:

      PARAMETER                              VALUE
      ===========================================================
      NLS_LANGUAGE                           AMERICAN
      NLS_TERRITORY                          AMERICA
      NLS_CURRENCY                           $
      NLS_ISO_CURRENCY                       AMERICA
      NLS_NUMERIC_CHARACTERS                 .,
      NLS_CALENDAR                           GREGORIAN
      NLS_DATE_FORMAT                        DD-MON-RR
      NLS_DATE_LANGUAGE                      AMERICAN
      NLS_CHARACTERSET                       UTF8
      NLS_SORT                               BINARY
      NLS_TIME_FORMAT                        HH.MI.SSXFF AM
      NLS_TIMESTAMP_FORMAT                   DD-MON-RR HH.MI.SSXFF AM
      NLS_TIME_TZ_FORMAT                     HH.MI.SSXFF AM TZR
      NLS_TIMESTAMP_TZ_FORMAT                DD-MON-RR HH.MI.SSXFF AM TZR
      NLS_DUAL_CURRENCY                      $
      NLS_NCHAR_CHARACTERSET                 UTF8
      NLS_COMP                               BINARY
      NLS_LENGTH_SEMANTICS                   BYTE
      NLS_NCHAR_CONV_EXCP                    FALSE

      19 rows selected.

    2. Type enter to return to the NLS menu.

    Archiving (Util O/7)

    Resetta backup and recovery procedures are based on Oracle. In order to provide complete recovery of data up to the time of failure, the Oracle database mode must be archivelog mode. This ensures full recovery up to the time of failure. Hot backup cannot be performed without first enabling archivelog mode of the database.
    If the database is in noarchivelog mode, only cold backups can be performed. A cold backup restores data to the time the last backup was performed. This means that all changes from the time of failure are lost.
    Ex Libris highly recommends archivelog mode because in this mode, both cold and hot backups can be used to recover the database right up to the time of the failure. To ensure a complete recovery, all the archive files that were generated from the time of the backup (hot or cold) until the time of failure must be available.
    Refer to the Oracle backup manual for more information on the preliminary actions that are required before using Util O/7.
    • Always perform a full cold backup immediately after switching to archivelog mode. If you do not do this, there will be a gap in the archivelog files, which will prevent a full recovery.
    The Archiving menu allows you to manage archiving.
    O.7 Archiving
    ---------------
    0. Exit Procedure
    1. Turning Archiving On
    2. Turning Archiving Off
    3. Show Archiving Status
     
    To access the Archiving menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 7 to display the Archiving menu.

    Turning Archiving On (Util O/7/1)

    When you run your database in ARCHIVELOG mode, you enable the archiving of the redo log.
    • Turning archiving on requires a EXL_DBA user name and password.
    • Changing the archiving mode shuts down the database and restarts it in archivelog mode.
    To turn on Oracle archiving mode:
    1. Stop Rosetta   using the startup scripts described in Starting and Stopping the System.
    2. From the Archiving menu, enter option 1 to turn on archiving.
    3. At the following prompt, enter the EXL_DBA/EXL_DBA user name/password:
      To continue you will need to enter EXL_DBA username/password.
      username/password:
    4. Restart Rosetta using the startup script described in Starting and Stopping the System.

    Turning Archiving Off (Util O/7/2)

    When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log.
    • Requires EXL_DBA user name and password.
    • Changing the archiving mode shuts down the database and restarts it with archivelog mode off.
    To turn off Oracle archiving mode:
    1. Stop Rosetta processes using the startup script, as described in Starting and Stopping the System.
    2. From the Archiving menu, enter option 2 to turn off archiving.
    3. At the following prompt, enter the EXL_DBA/EXL_DBA user name/password:
      To continue you will need to enter EXL_DBA username/password.
      username/password:
    4. Restart Rosetta using the startup scripts described in Starting and Stopping the System.

    Show Archiving Status (Util O/7/3)

    This utility displays the current archiving status of Oracle.
    You will need an EXL_DBA user name and password to view the status.
    To view the archiving status, enter option 3 from the Archiving menu to view the status. For example, the following output displays if archiving is off:
    SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 7 14:09:12 2007
    Copyright (c) 1982, 2005, Oracle. All rights reserved.
    idle> Connected.
    idle> idle> Database log mode No Archive Mode
    Automatic archival Disabled
    Archive destination /exlibris/oradata/prm1/arch/
    Oldest online log sequence 6717
    Current log sequence 6721
    idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining Scoring Engine options
    When archiving is on, the following two lines are displayed, as follows:
    SQL> SQL> Database log mode Archive Mode
    Automatic archival Enabled
     
    In a production database, always set the Database log mode to Archive mode.

    Database Users (Util O/9)

    The Database Users menu allows you to view and create database users.
    O.9. Database Users
    -------------------
    0. Exit Procedure
    1. List Database Users
    2. Create a New User
    Please select [exit]:
     
    To access the Database Users menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 9 to display the Database Users menu.

    List Database Users (Util O/9/1)

    This utility lists all of the users in the database. To list the database users, enter option 1 from the Database Users menu. For example:
    The Database dps Contains the Following Users:
    ======================================================
    :V23D_DEP00
    ANONYMOUS
    APEX_030200
    APEX_040200
    APEX_PUBLIC_USER
    APPQOSSYS
    AUDSYS
    CTXSYS
    DBSNMP
    DIP
    EXL
    EXL_ADMIN
    EXL_BACKUP
    EXL_DBA
    FLOWS_FILES
    GSMADMIN_INTERNAL
    GSMCATUSER
    GSMUSER
    MDSYS
    OJVMSYS
    OLAPSYS
    ORACLE_OCM
    ORDDATA
    ORDPLUGINS
    ORDSYS
    OUTLN
    SI_INFORMTN_SCHEMA
    SYS
    SYSBACKUP
    SYSDG
    SYSKM
    SYSTEM
    V23D_DEP00
    V23D_JBPM00
    V23D_PER00
    V23D_PRSV00
    V23D_PUB00
    V23D_REP00
    V23D_ROS00
    V23D_RPT00
    V23D_SHR00
    V2M9_JBPM00
    V2M9_PRSV00
    V2M9_RPT00
    V2M9_SEARCH00
    WMSYS
    XDB
    XS$NULL
    Enter CR to continue...
     
    Some of the users are Rosetta users and others are administrative users.

    Create a New User (Util O/9/2)

    This utility creates a new user with a default password that is the same as the user name.
    If the name of the user already exists, all the tables and data of that user are deleted. The user is created with empty tables.
    To create a new database user:
    1. Enter option 2 from the Database Users menu.
    2. At the following prompt, enter the new user name:
      Enter User Name to Create New User:
    3. At the following prompt, enter yes to continue:
      enter yes to create oracle user <new_user/>
    4. At the following prompt, enter no to reconfirm:
      default password is v23d_<new_user/>
      if user <new_user/> exists all data will be erased!!!
      enter no to reconfirm
    5. At the following prompt, type enter to return to the Database Users menu:
      source create_ora_user_b v23d_<new_user/>
      create_ora_user_b v23d_<new_user/>
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 06:46:35 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      Enter user-name:
      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      SQL> EXIT
      Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 06:46:35 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      Enter user-name:
      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      SQL> old 1: DROP USER &&1 CASCADE
      new 1: DROP USER v23d_<new_user/> CASCADE
      DROP USER v23d_<new_user/> CASCADE
      *
      ERROR at line 1:
      ORA-01918: user 'v23d_<new_user/>' does not exist
      User created.
      Grant succeeded.
      Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...

    Database Files (Util O/13)

    The Database Files menu contains utilities that allow you to manage database files.
    O.13 Database Files
    ---------------
    0. Exit Procedure
    1. List of Database Files
    2. Resize Oracle Datafile
    3. Add File to Tablespace
    4. Show Datafile Free Blocks by KBytes
    5. Show Datafile Free Blocks by BlockID
    Please select [exit]:
     
    To access the Database Files menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 13 to display the Database Files menu.

    List of Database Files (Util O/13/1)

    This utility lists the Oracle data files and their sizes. To list the database files, enter option 1 from the Database Files menu. For example:

    The Database dps Contains the Following Files:
    ======================================================

    T           NAME                                                                                  SIZE K  F
    ----------  --------------------------------------------------  ----------------------------------------  ---
    LOG         /exlibris/oradata/dps/dps_log01.dbf                                                    65536  6
    SYSAUX      /exlibris/oradata/dps/dps_sysaux01.dbf                                               3145728  2
    SYSTEM      /exlibris/oradata/dps/dps_system01.dbf                                                964608  1
    TS0         /exlibris/oradata/dps/dps_ts0_01.dbf                                                20971520  7
    TS1         /exlibris/oradata/dps/dps_ts1_01.dbf                                                  393216  8
    TSLOB       /exlibris/oradata/dps/dps_tslob01.dbf                                                 512000  5
    TS_DEF_DAT  /exlibris/oradata/dps/dps_ts_def_dat_01.dbf                                          1048576  100
    TS_DEP_DAT  /exlibris/oradata/dps/dps_ts_dep_dat_01.dbf                                           131072  28
    TS_DEP_DAT  /exlibris/oradata/dps/dps_ts_dep_dat_02.dbf                                           131072  29
    TS_DEP_DAT  /exlibris/oradata/dps/dps_ts_dep_dat_03.dbf                                           131072  30
    TS_DEP_DAT  /exlibris/oradata/dps/dps_ts_dep_dat_04.dbf                                           131072  31
    TS_DEP_DAT  /exlibris/oradata/dps/dps_ts_dep_dat_05.dbf                                           131072  32

    Enter CR to continue...
     

    Resize Oracle Datafile (Util O/13/2)

    This utility allows you to enlarge or reduce the size of an Oracle data file.
    Requires the EXL_DBA user name and password.
    To change the size of an Oracle data file:
    1. Enter option 2 from the Database Files menu.
    2. At the following prompt, enter the EXL_DBA/EXL_DBA user name/password:
      To resize a database file enter EXL_DBA username/password.
      username/password:
    3. At the following prompt, enter the name of the tablespace:
      Enter Tablespace name:
    4. At the following prompt, enter the file name (including the full path) that you want to resize:
      Enter file name to resize:
    5. At the following prompt, enter the new file size:
      Enter new file size (MB):
    6. At the following prompt, enter y to continue:
      confirm (y/[n]):
    7. At the following prompt, type enter to continue:
      resizing
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 00:19:27 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      idle> Connected.
      idle> idle>
      Database altered.
      idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...

    Add File to Tablespace (Util O/13/3)

    Tablespaces are composed of one or more data files. This utility allows you to add files to tablespaces. If a tablespace does not have enough free space for these files, it needs to be expanded. For information on expanding tablespaces, see Database Tablespaces (Util O/17).
    This utility requires the EXL_DBA user name and password.
    To add a data file to a tablespace:
    1. Enter option 3 from the Database Files menu.
    2. At the following prompt, enter the EXL_DBA/EXL_DBA user name/password:
      To add a file to a tablespace enter EXL_DBA username/password.
      username/password:
    3. At the following prompt, enter the name of the tablespace:
      Enter Tablespace name:
    4. At the following prompt, enter the new file name (including the full path):
      Enter new file name:
    5. At the following prompt, enter the file size:
      Enter file size (MB):
    6. At the following prompt, enter y to continue:
      confirm (y/[n]):
    7. At the following prompt, type enter to continue:
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 00:44:50 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      idle> Connected.
      idle> idle>
      Tablespace altered.
      idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...

    Show Datafile Free Blocks by Kbytes (Util O/13/4)

    This utility displays the free data file blocks. To display the free data file blocks, enter option 4 to display from the Database Files menu. For example:

    Free Blocks Report by Kbytes
    ==========================================

    TABLES  F                                    BLOCK_ID                                    KBYTES  NAME
    ------  ---  ----------------------------------------  ----------------------------------------  -----------------------------------
    TS_SHR  15                                    1015936                                   4063232  /exlibris/oradata/dps/dps_ts_shr_id
    _IDX                                                                                             x_01.dbf

    TS_SHR  15                                     508032                                   4063232  /exlibris/oradata/dps/dps_ts_shr_id
    _IDX                                                                                             x_01.dbf

    TS_SHR  15                                    1523840                                   4063232  /exlibris/oradata/dps/dps_ts_shr_id
    _IDX                                                                                             x_01.dbf

    TS_SHR  15                                    2031744                                   4063232  /exlibris/oradata/dps/dps_ts_shr_id
    _IDX                                                                                             x_01.dbf

    Enter CR to continue...
     

    Show Datafile Free Blocks by BlockID (Util O/13/5)

    This utility displays the free data file blocks per tablespace.
    To display the free data file blocks:
    1. Enter option 5 from the Database Files menu.
    2. At the following prompt, enter the name of the tablespace:
      Tablespace Name:
    3. At the following prompt, specify the datafile number of the tablespace:
      Datafile Number:
      To determine the datafile number, see List of Database Files (Util O/13/1).
    4. At the following prompt, type enter to return to the Database Files menu:

      Tablespace Name: TS0
      Datafile Number: 7
      Enter value for ts: Enter value for fl_no: 
                                      BLOCK_ID                                     BYTES
      ----------------------------------------  ----------------------------------------
                                       2539648                                 670040064
                                       2487680                                 425721856
                                       2001952                                    786432
                                       1438744                                    851968
                                       1438016                                    524288
                                       1437552                                    131072
                                       1435240                                    196608
                                       1378232                                    589824
                                       1024368                                     65536
                                         87008                                     65536
                                         72528                                     65536

      Enter CR to continue...

       

    Database Free/Used Space (Util O/14)

    The Database Free/Used Space menu allows you to display information about the free and used space in the tablespace.
        O.14. Database Free/Used Space
        ------------------------------
           0. Exit Procedure
           1. All Tablespaces Free Space Summary 
           2. Number of Free Extents by Size in a Tablespace
           3. All Free Extents of Min Size in a Tablespace
           4. Space Used by a Unit/Units in Each Tablespace
           5. Space Used by a Group of Units in Each Tablespace
           6. Coalesce Contiguous Free Extents
           8. Primo Tablespaces Total/Free/Used Space Report
           9. Clean Temporary Tablespace Free Storage
     
    To access the Database Free/Used Space menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 14 to display the Database Free/Used Space menu.

    All Tablespaces Free Space Summary (Util O/14/1)

    This utility provides details about free space in the Oracle DBA_FREE_SPACE table. It generates a report with the following four columns:
    • TABLESPACE_NAME: The name of the tablespace.
    • TOTAL_FREE_SPACE: The total amount of free space in the tablespace (in megabytes).
    • MAX_EXTENT: The size of the largest contiguous extent of the tablespace (in megabytes).
    • NUM_FREE_EXTENTS: The number of free extents in the tablespace.
    If a tablespace has no free space left, it will not appear in the report.
    It is important to review this report from time to time to prepare additional resources for the database.
    To display a summary of the free space for tablespaces, enter option 1 from the Database Free/Used Space menu. For example:

    TABLESPACE_NAME                                         TOTAL_FREE_SPACE                                MAX_EXTENT                          NUM_FREE_EXTENTS
    ------------------------------  ----------------------------------------  ----------------------------------------  ----------------------------------------
    TS0                             1048.70106870229007633587786259541984733  639.351206106870229007633587786259541985                                        11
    TS_HFRE_DAT                     5245.88164885496183206106870229007633588  1151.63261068702290076335877862595419847                                         5
    SYSAUX                          979.162870229007633587786259541984732824  918.504549618320610687022900763358778626                                       107
    UNDOTBS1                        3904.20714503816793893129770992366412214  2491.36854961832061068702290076335877863                                        43
    TSLOB                           408.036641221374045801526717557251908397  395.467236641221374045801526717557251908                                         7
    TS_EXL_DEF                      127.069801526717557251908396946564885496  127.069801526717557251908396946564885496                                         1
    TS_SHR_DAT                      3918.52751145038167938931297709923664122  821.451236641221374045801526717557251908                                        71
    TS_SHR_IDX                      26651.0773740458015267175572519083969466  3970.18088549618320610687022900763358779                                        96
    TS_DEP_DAT                       599.07908396946564885496183206106870229  118.064854961832061068702290076335877863                                        20
    TS_HDEC_IDX                     245.634931297709923664122137404580152672  117.314442748091603053435114503816793893                                         9
    TS_JBPM_DAT                     635.349007633587786259541984732824427481  127.069801526717557251908396946564885496                                         5
    TS_JBPM_IDX                     127.069801526717557251908396946564885496  127.069801526717557251908396946564885496                                         1
    TS_HDEC_DAT                     635.349007633587786259541984732824427481  127.069801526717557251908396946564885496                                         5
    TS_HDES_DAT                     635.349007633587786259541984732824427481  127.069801526717557251908396946564885496                                         5
    USERS                            7.9340458015267175572519083969465648855   7.9340458015267175572519083969465648855                                         1
    LOG                             63.0346259541984732824427480916030534351  63.0346259541984732824427480916030534351                                         1
    TS_DEP_IDX                      125.381374045801526717557251908396946565  125.381374045801526717557251908396946565                                         1
    TS_HFRE_IDX                     2863.57300763358778625954198473282442748  67.0368244274809160305343511450381679389                                       150
    TS_SEARCH_DAT                    2158.6858015267175572519083969465648855  604.331969465648854961832061068702290076                                         6
    TS_DEF_DAT                      1023.56225954198473282442748091603053435  1023.56225954198473282442748091603053435                                         1
    SYSTEM                          31.9550534351145038167938931297709923664  2.00109923664122137404580152671755725191                                       127

    TABLESPACE_NAME                                         TOTAL_FREE_SPACE                                MAX_EXTENT                          NUM_FREE_EXTENTS
    ------------------------------  ----------------------------------------  ----------------------------------------  ----------------------------------------
    TS_REP_IDX                      46.4004885496183206106870229007633587786  46.0252824427480916030534351145038167939                                         4
    TS_HDEB_DAT                     635.349007633587786259541984732824427481  127.069801526717557251908396946564885496                                         5
    TS_HDEB_IDX                     253.764396946564885496183206106870229008  127.069801526717557251908396946564885496                                         3
    TS_HDEP_IDX                     249.199389312977099236641221374045801527  119.503145038167938931297709923664122137                                         6
    TS_SEARCH_IDX                   127.007267175572519083969465648854961832  127.007267175572519083969465648854961832                                         1
    TS_PRSV_IDX                     118.252458015267175572519083969465648855  118.252458015267175572519083969465648855                                         1
    TS_HDEP_DAT                     635.349007633587786259541984732824427481  127.069801526717557251908396946564885496                                         5
    TS1                             129.070900763358778625954198473282442748  107.058809160305343511450381679389312977                                         3
    TS_PER_DAT                      634.098320610687022900763358778625954198  127.069801526717557251908396946564885496                                         6
    TS_PER_IDX                       120.75383206106870229007633587786259542  110.060458015267175572519083969465648855                                         7
    TS_HDEM_IDX                     252.638778625954198473282442748091603053   66.036274809160305343511450381679389313                                         6
    TS_PUB_IDX                      126.319389312977099236641221374045801527  125.944183206106870229007633587786259542                                         3
    TS_REP_DAT                       192.66833587786259541984732824427480916  125.068702290076335877862595419847328244                                        22
    TS_PRSV_DAT                     419.230290076335877862595419847328244275   126.06925190839694656488549618320610687                                         5
    TS_HDEM_DAT                     1403.77111450381679389312977099236641221  639.351206106870229007633587786259541985                                         5
    TS_HDES_IDX                     252.638778625954198473282442748091603053  125.068702290076335877862595419847328244                                         5
    TS_PUB_DAT                       626.84433587786259541984732824427480916   126.06925190839694656488549618320610687                                         6

    Temporary Tablespace Space Usage

    TABLESPACE_NAME                                                   SIZE M
    ------------------------------  ----------------------------------------
    TEMP                                                                   0

    Sort Segments Usage (in Temporary Tablespace)

    TABLESPACE_NAME                                                  Total M                                    Used M                                    Free M
    ------------------------------  ----------------------------------------  ----------------------------------------  ----------------------------------------
    TEMP                                                                5119                                        12                                      5107

    Enter CR to continue... 

     

    Number of Free Extents by Size in a Tablespace (Util O/14/2)

    This utility lists the number of extents of a certain size (truncated in megabytes) in the tablespace.
    To display the free extents for a tablespace, enter
    1. Enter option 2 from the Database Free/Used Space menu
    2. At the following prompt, enter the tablespace name:
      Enter Tablespace name:
    3. At the following prompt, type enter to return to the Database Free/Used Space menu:

      Enter Tablespace name: TS0


                                    SIZE IN MB                            NUM OF EXTENTS
      ----------------------------------------  ----------------------------------------
                                           639                                         1
                                           406                                         1
                                             0                                         9

      Enter CR to continue... 
       

       

    All Free Extents of Min Size in a Tablespace (Util O/14/3)

    This utility lists the exact size (in megabytes) of all free extents that are larger than a given size. You are prompted for the tablespace name and the minimum size (in megabytes) that you wish to investigate.
    To display the free extents for a tablespace:
    1. Enter option 3 from the Database Free/Used Space menu.
    2. At the following prompt, enter the tablespace name:
      Enter Tablespace name:
    3. At the following prompt, enter the minimum size of the extents to display. Enter to 0 to display all of the extents.
      Enter Min size (MB) of free extent [0=ALL]:
    4. At the following prompt, type enter to return to the Database Free/Used Space menu:

      Enter Tablespace name: TS0
      Enter Min size (MB) of free extent [0=ALL]: 0


                                   EXTENT_SIZE
      ----------------------------------------
      639.351206106870229007633587786259541985
      406.223145038167938931297709923664122137
      .812946564885496183206106870229007633588
      .750412213740458015267175572519083969466
      .562809160305343511450381679389312977099
      .500274809160305343511450381679389312977
      .187603053435114503816793893129770992366
      .125068702290076335877862595419847328244
      .062534351145038167938931297709923664122
      .062534351145038167938931297709923664122
      .062534351145038167938931297709923664122

      Enter CR to continue... 

    Coalesce Contiguous Free Extents (Util O/14/6)

    This utility is not required when using a locally managed tablespace. It is retained for backward compatibility (and will be removed in future versions).
    This utility requires the EXL_DBA user name and password.
    Free space in a database may be composed of extents of various sizes. It is worthwhile to use this procedure to coalesce the contiguous free extents to create larger free extents. Perform this procedure periodically.
    To coalesce free extents:
    1. Enter option 6 from the Database Free/Used Space menu.
    2. At the following prompt, enter the EXL_DBA user name and password:
      To Coalesce Tablespaces enter EXL_DBA username/password.
      username/password:
    3. At the following prompt, type enter to return to the Database Free/Used Space menu:
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 20:37:29 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      SQL> alter tablespace LOG coalesce;
      alter tablespace SYSAUX coalesce;
      alter tablespace SYSTEM coalesce;
      alter tablespace TS0 coalesce;
      alter tablespace TS1 coalesce;
      alter tablespace TSLOB coalesce;
      alter tablespace TS_P_DAT coalesce;
      alter tablespace TS_P_IDX coalesce;
      alter tablespace USERS coalesce;
      Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 20:37:29 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      SQL> Connected.
      SQL> SQL>
      'ALTERTABLESPACE'||TABLESPACE_NAME||'COALESCE;'
      -----------------------------------------------------------
      alter tablespace LOG coalesce;
      alter tablespace SYSAUX coalesce;
      alter tablespace SYSTEM coalesce;
      alter tablespace TS0 coalesce;
      alter tablespace TS1 coalesce;
      alter tablespace TSLOB coalesce;
      alter tablespace TS_P_DAT coalesce;
      alter tablespace TS_P_IDX coalesce;
      alter tablespace USERS coalesce;
      9 rows selected.
      Tablespace altered.
      Tablespace altered.
      SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...

    Tablespaces Total/Free/Used Space Report (Util O/14/8)

    This utility lists the total space that each repository occupies in each tablespace and the amount of free space in the table. To display the total space each repository occupies, enter option 8 from the Database Free/Used Space menu.
    For example:

    NAME                                   TOTAL SIZE M
    ---------  ----------------------------------------
    TS0                                           20480
    TS_HFRE_D                                      5248
    AT

    TSLOB                                           500
    TS_EXL_DE                                       128
    F

    TS_SHR_DA                                      4736
    T

    TS_SHR_ID                                     32128
    X

    TS_DEP_DA                                       640
    T

    TS_HDEC_I                                       256
    DX

    TS_JBPM_D                                       640
     

     

    Clean Temporary Tablespace Free Storage (Util O/14/9)

    In some cases, the temporary tablespace does not free non-used pages quickly enough. This utility manually frees these pages.
    To clean temporary tablespace:
    1. Enter option 9 from the Database Free/Used Space menu.
    2. At the following prompt, enter the name of the temporary tablespace:
      To Free Temporary Segments Enter Temporary Tablespace:
    3. At the following prompt, enter the EXL_DBA/EXL_DBA user name and password:
      Enter EXL_DBA username/password:
    4. At the following prompt, type enter to return to the Database Free/Used Space menu:
      QL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 12:52:41 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      idle> Connected.
      idle>
      Tablespace altered.
      idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...

    Database Tablespaces (Util O/17)

    The Database Tablespace menu allows you to manage tablespaces.
    O.17. Database Tablespaces
    --------------------------
    0. Exit Procedure
    1. Create a Tablespace
    2. List Tablespace Files
    4. Show Tablespace Definitions
    5. Show Tablespace Allocated/Free/Used Space
    Please select [exit]:
     
    To access the Database Tablespaces menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 17 to display the Database Tablespaces menu.

    Create a Tablespace (Util O/17/1)

    Typically, all the required tablespaces are created during system installation. This utility allows you to create additional tablespaces.
    To create a tablespace:
    1. Enter option 1 from the Database Tablespaces menu.
    2. At the following prompt, enter the EXL_DBA/EXL_DBA user name and password:
      To Create a new Tablespace, Enter EXL_DBA username/password.
      username/password:
    3. At the following prompt, enter the name of the new tablespace:
      Enter Tablespace name:
    4. At the following prompt, enter the new file name (include full path):
      Enter new file name (full path) :
    5. At the following prompt, enter the size of the file:
      Enter new file size (MB):
    6. At the following prompt, enter the allocation type (AUTO or UNIFORM):
      ==============================================================
      Tablespaces can be created with a UNIFORM size for all extents
      or with allocation type AUTOALLOCATE which means
      Oracle will decide how to define extents
      Util o 17 4 can be used to see current definitions
      for existing tablespaces
      ==============================================================
      Tablespace Allocation Type : [AUTO/UNIFORM]
    7. If you have selected UNIFORM, enter the uniform size of each extent:
      UNIFORM SIZE : [128K/1M/4M/128M/1920M]
    8. Athe following prompt, enter y to confirm:
      Tablespace: TEST1
      File: /exlibris/oradata/prm0/test1_01.dbf
      File size: 100MB
      Allocation : UNIFORM SIZE 4M
      confirm (y/[n]):
    9. At the following prompt, enter y to continue:
      CREATE TABLESPACE TEST1
      DATAFILE '/exlibris/oradata/prm0/test1_01.dbf' SIZE 100M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M
      SEGMENT SPACE MANAGEMENT AUTO
      ONLINE;
      Enter CR to continue...
    10. At the following prompt, enter y to continue:
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 15:13:43 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      idle> Connected.
      idle> idle> 2 3 4 5
      Tablespace created.
      idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...
    11. At the following prompt, type enter to return to the Database Tablespaces menu:
      SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 15:13:43 2009
      Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
      idle> Connected.
      idle> idle> 2 3 4 5
      Tablespace created.
      idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Enter CR to continue...

    List Tablespace Files (Util O/17/2)

    This utility lists the tablespaces in the database.
    To list a tablespace’s files:
    1. Enter option 2 from the Database Tablespaces menu.
    2. At the following prompt, enter the name of the tablespace:
      Enter Tablespace name:
    3. At the following prompt, type enter to return to the Database Tablespaces menu:

      Enter Tablespace name: TS0

      Tablespace TS0 consist of the following files:

      NAME                                                                                  SIZE K  F
      --------------------------------------------------  ----------------------------------------  ---
      /exlibris/oradata/dps/dps_ts0_01.dbf                                                20971520  7
       
      Enter CR to continue... 

    Show Tablespaces Definition (Util O/17/4)

    You may need to increase the space that certain database tables use. All tablespaces in Rosetta are created as locally-managed tablespaces. When working with locally-managed tablespaces, the word local appears in the EXT-MGMT (extent management) column.
    When using locally-managed tablespaces, the following types of extent allocation appear in the ALLOC_TYP column:
    • SYSTEM (Auto Allocate)—In auto allocation, Oracle automatically assumes full control, allocating extents as needed and taking into account the initial allocation of the table/index as configured in the create table/index command. For example, if initial allocation of the table/index is 1GB, Oracle might split the 1GB into 50 extents, a single extent, or any other combination of extents.
    • UNIFORM—In uniform allocation, the DBA determines a standard size for all the extents in the tablespace. All the extents in the tablespace are of that size, without regard to the extent definitions in the Create Table command. The DBA determines which table is assigned to which tablespace depending on the table size. Hence, in uniform allocation, there is no fragmentation, and space utilization is optimal. For example, when creating a tablespace with a uniform extent size of 10 MB, five extents will be used for a table of 50 MB.
    To display the tablespace definitions, enter option 4 from the Database Tablespaces menu.

    TS_NAME       EXT_MGMT    ALLOC_TYP       INIT_EXT    NEXT_EXT  TYPE  STAT
    ------------  ----------  ---------  -------------  ----------  ----  ----
    LOG           LOCAL       SYSTEM             65536              PERM  ONL
    SYSAUX        LOCAL       SYSTEM             65536              PERM  ONL
    SYSTEM        LOCAL       SYSTEM             65536              PERM  ONL
    TEMP          LOCAL       UNIFORM          1048576     1048576  TEMP  ONL
    TS0           LOCAL       SYSTEM             65536              PERM  ONL
    TS1           LOCAL       SYSTEM             65536              PERM  ONL
    TSLOB         LOCAL       SYSTEM             65536              PERM  ONL
    TS_DEF_DAT    LOCAL       SYSTEM             65536              PERM  ONL
    TS_DEP_DAT    LOCAL       SYSTEM             65536              PERM  ONL
    TS_DEP_IDX    LOCAL       SYSTEM             65536              PERM  ONL
    TS_EXL_DEF    LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEB_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEB_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEC_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEC_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEM_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEM_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEP_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDEP_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDES_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HDES_IDX   LOCAL       SYSTEM             65536              PERM  ONL

    TS_NAME       EXT_MGMT    ALLOC_TYP       INIT_EXT    NEXT_EXT  TYPE  STAT
    ------------  ----------  ---------  -------------  ----------  ----  ----
    TS_HFRE_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_HFRE_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_JBPM_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_JBPM_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_PER_DAT    LOCAL       SYSTEM             65536              PERM  ONL
    TS_PER_IDX    LOCAL       SYSTEM             65536              PERM  ONL
    TS_PRSV_DAT   LOCAL       SYSTEM             65536              PERM  ONL
    TS_PRSV_IDX   LOCAL       SYSTEM             65536              PERM  ONL
    TS_PUB_DAT    LOCAL       SYSTEM             65536              PERM  ONL
    TS_PUB_IDX    LOCAL       SYSTEM             65536              PERM  ONL
    TS_REP_DAT    LOCAL       SYSTEM             65536              PERM  ONL
    TS_REP_IDX    LOCAL       SYSTEM             65536              PERM  ONL
    TS_SEARCH_DA  LOCAL       SYSTEM             65536              PERM  ONL
    T

    TS_SEARCH_ID  LOCAL       SYSTEM             65536              PERM  ONL
    X

    TS_SHR_DAT    LOCAL       SYSTEM             65536              PERM  ONL
    TS_SHR_IDX    LOCAL       SYSTEM             65536              PERM  ONL
    UNDOTBS1      LOCAL       SYSTEM             65536              UNDO  ONL

    TS_NAME       EXT_MGMT    ALLOC_TYP       INIT_EXT    NEXT_EXT  TYPE  STAT
    ------------  ----------  ---------  -------------  ----------  ----  ----
    USERS         LOCAL       UNIFORM            40960       40960  PERM  ONL
     
    Enter CR to continue... 
     

     
    In addition to the extent management types, this utility display the following information for each tablespace:
    • Segment allocation
    • Tablespace (for permanent or temporary objects or for undo segments)
    • Tablespace status (online or offline)

    Show Tablespace Allocated/Free/Used Space (Util O/17/5)

    This utility displays the following parameters for a tablespace:
    • Total tablespace size
    • Amount of free space
    • Amount of used space
    To display allocated, free, and used space for a tablespace:
    1. Enter option 5 from the Database Tablespaces menu.
    2. At the following prompt, enter the name of the tablespace:
      Enter Tablespace name :
    3. At the following prompt, type enter to return to the Database Tablespaces menu:

      Enter Tablespace name : TS0
      Tablespace TS0 :
      _____________________________

                                  TOTAL SIZE M
      ----------------------------------------
                                         20480

                                  TOTAL FREE M
      ----------------------------------------
                                          1048

                                  TOTAL USED M
      ----------------------------------------
                                         19430

      Enter CR to continue... 

    Oracle Statistics (Util O/18)

    The Oracles Statistics menu allows you to display Oracle statistics.
        O.18. Oracle Statistics
        -----------------------
           0. Exit Procedure
           1. Performance Statistics
           2. Rollback Segments Definitions
           3. Rollback Segments Dynamic Allocation
           4. View Long Operations
           5. IO Statistics
           6. Sort Operations
    Please select [exit]: 
     
    To access the Oracle Statistics menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 18 to display the Oracle Statistics menu.

    Performance Statistics (Util O/18/1)

    This utility provides Oracle performance statistics for fine-tuning your database. To display the performance statistics, enter option 1 from the Oracle Statistics menu.

    SYSTEM GLOBAL AREA (sga)
                                       BYTES
                                  1577058304
    =======================================================
    BUFFER CACHE HIT RATIO (db_block_buffers)
                                        GETS                                    MISSES  RATIO
                                 20163618477                                 885918809  95.61%
    =======================================================
    STATISTIC (db_block, DBWR, sort_area)
    NAME                                          VALUE
    opened cu                                       558
    rsors cur
    rent
    db block                                 1167140510
    gets
    consisten                               18996477969
    t gets
    physical                                  885918809
    reads
    physical                                    8233215
    writes
    DBWR chec                                     79830
    kpoints
    redo log                                        635
    space req
    NAME                                          VALUE
    uests
    sorts (me                                   6336373
    mory)
    sorts (di                                         0
    sk)

    Enter CR to continue... 

    Rollback Segments Definitions (Util O/18/2)

    This utility displays the rollback segments definitions.
    To display the rollback segments definitions:
    1. Enter option 2 from the Oracle Statistics menu.
    2. At the following prompt, type enter to continue:
      SYSTEM GLOBAL AREA (sga)
      BYTES
      734003200
      =======================================================
      BUFFER CACHE HIT RATIO (db_block_buffers)
      GETS MISSES RATIO
      4054336 225243 94.44%
      =======================================================
      STATISTIC (db_block, DBWR, sort_area)
      NAME VALUE
      opened cursors current 32
      db block gets 937066
      consistent gets 3117272
      physical reads 225243
      physical writes 92082
      DBWR checkpoints 37
      redo log space requests 0
      sorts (memory) 105305
      sorts (disk) 1
      Enter CR to continue...
    3. At the following prompt, type enter to return to the Oracle Statistics menu:
      =======================================================
      DATA DICTIONARY CACHE (shared_pool_size)
      GETS MISSES RATIO
      1442213 22892 98.41%
      =======================================================
      LIBRARY CACHE (shared_pool_size)
      EXECUTIONS MISSES LIBCACHEPROZ
      552613 1160 99.79%
      Enter CR to continue...

    Rollback Segments Dynamic Allocation (Util O/18/3)

    This utility displays the rollback segments dynamic allocation. To display the rollback segments dynamic allocation, enter option 3 from the Oracle Statistics menu. For example:

    NAME   EXT        RSSIZE      WRITES  SHRN    AVGSHR  WRAPS  CUREXT     WAITS
    SYST     6        385024       29064     0         0      1       1         0
    _SYS     3       1171456  ##########   309   4014663   ####       2       113
    _SYS     3       1171456  ##########   304   3679070   ####       2        88
    _SYS     3       1171456  ##########   308   3754063   ####       2       107
    _SYS     3       1171456  ##########   284   4133613   ####       2       102
    _SYS     3       1171456  ##########   317   3719426   ####       1        90
    _SYS     3       1171456  ##########   314   3783138   ####       2        87
    _SYS     3       1171456  ##########   295   3865957   ####       1        93
    _SYS     3       1171456  ##########   298   3816042   ####       2        98
    _SYS     3       1171456  ##########   303   3960710   ####       1       105
    _SYS     3       1171456  ##########   314   3774998   ####       2       112
    _SYS     2        122880  ##########   303   3645575   ####       1       100
    _SYS     3       1171456  ##########   317   3863729   ####       2        95
    _SYS     3       1171456  ##########   281   3891578   ####       2        99
    _SYS     3       1171456  ##########   287   3807710   ####       2        91
    _SYS     3       1171456  ##########    27   1509755    185       2       125
    _SYS     4        253952  ##########    14   1380937     84       0       100
    _SYS     3        188416  ##########    16   1900544     95       1       107
    _SYS     4       2220032   233278526     2   1048576     12       2        18
    _SYS     3       1171456   234901688     2   2097152     18       1        19
    _SYS     3       1171456     7460096     2   1572864     17       2         0
    NAME   EXT        RSSIZE      WRITES  SHRN    AVGSHR  WRAPS  CUREXT     WAITS
    _SYS     3       1171456     5751310     6    644437     50       2         4
    _SYS     3        1171456  ##########   285   3906635   ####       1      113
    _SYS     3         188416  ##########   302   3921743   ####       0      111
    _SYS     3        1171456  ##########   314   3849718   ####       2      100
    _SYS     4        2220032  ##########   300   3982185   ####       2       85
    _SYS     3        1171456  ##########   280   3929585   ####       2      100
    _SYS     3        1171456  ##########   299   3786841   ####       2       84
    _SYS     3        1171456  ##########   296   4095557   ####       2      104
    _SYS     4        2220032  ##########   291   4166603   ####       3       95

    Enter CR to continue... 

    View Long Operations (Util O/18/4)

    This utility displays Oracle long operations, if they occur in the system at the time the utility is run. The following information displays:
    • SID: session identifier.
    • OPNAME: operation name.
    • TARGET: the object on which the operation is being performed.
    • DONE SO FAR: percentage of work already done.
    To display the Oracle long operations:
    1. Enter the following commands to display the Managing Oracle menu:
      util o
    2. Enter option 18 to display the Oracle Statistics menu.
    3. Enter option 4.
    4. At the following prompt, type cntl-c to exit the display:
      Long Operation Currently running :
      To stop do Ctrl C

    IO Statistics (Util O/18/5)

    This utility displays the following information:
    • BLOCK_GETS—block gets for this session.
    • CONSISTENT_GETS—consistent gets for this session.
    • PHYSICAL_READS—physical reads for this session.
    • BLOCK_CHANGES—block changes for this session.
    • CONSISTENT_CHANGES—consistent changes for this session.
    To display I/O statistics, enter option 5 from the Oracle Statistics menu. For example:
    TO STOP DO ctrl C
    BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
    1106249        86187425       2012771        553254         36893                                                                                                                                       
    1106270        86187335       2012766        553279         36893                                                                                                                                       
    1106362        86187448       2012767        553383         36894   
    Type cntl-c to exit the display.

    Sort Operations (Util O/18/6)

    This utility displays sort operations if they occur in the system when the utility is running. To display sort operations, enter option 6 from the Oracle Statistics menu. For example:
    Sort Operations in the system: 
    V23D_ROS00     793      60991TEMP      TEMPORARYLOB_DATA      1     128  
    V23D_ROS00     788       5787TEMP      TEMPORARYLOB_DATA      1     128  
    V23D_ROS00     776      12755TEMP      TEMPORARYLOB_DATA      1     128  
    V23D_ROS00     408      34112TEMP      TEMPORARYLOB_DATA      1     128  
    V23D_ROS00     405      17903TEMP      TEMPORARYLOB_DATA      1     128
    Type cntl-c to exit the display

    Shared Pool (Util O/19)

    The Shared Pool menu allows you to display information associated with shared pools.
    O.19. Shared Pool
    -----------------
    0. Exit Procedure
    1. Show SGA Buffers
    2. Flush Shared Pool
    Please select [exit]:
     
    To access the Shared Pool menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 19 to display the Oracle Statistics menu.

    Show SGA Buffers (Util O/19/1)

    This utility lists the various SGA buffers. To display SGA buffers, enter option 1 from the Shared Pool menu. For example:
    NAME                                          BYTES
    ---------------------------  ----------------------
    fixed_sga                                   2924832
    buffer_cache                              335544320
    log_buffer                                 13848576
    shared_io_pool                             33554432
    KSFD SGA I/O b                              4190360
    KTMC hash latch cleanup                       14336
    Enter CR to continue...

    Flush Shared Pool (Util O/19/2)

    This utility removes all of the Oracle objects from the shared pool.
    To remove the Oracle objects from the shared pool:
    1. Enter option 2 from the Shared Pool menu.
    2. At the following prompt, enter the EXL_DBA user name and password:
      To continue you will need to enter EXL_DBA/EXL_DBA username/password.
      username/password:

    Multi Threaded Server (Util O/20)

    In a standard Oracle configuration, a separate server process is created on behalf of each user process. This is called a Dedicated Server Process (or Shadow process) because it acts only on behalf of the associated user process.
    Oracle also supports the Shared Server Architecture (or Multi Threaded Server Architecture - MTS) in which there are several server processes, each serving several user processes.
    In Rosetta, The MTS infrastructure exists in the database but is only implemented in special cases, and in coordination with Ex Libris. This group of utilities supports MTS.
    The following menu allows you to manage these servers.
    O.20. Multi Threaded Server
    ---------------------------
    0. Exit Procedure
    1. Show MTS Parameters
    2. Show Listener Services
    Please select [exit]:
     
    To access the Multi Threaded Server menu:
    1. Log on to the server with the dps user.
    2. Enter the following commands to display the Managing Oracle menu.
      util o
    3. Enter option 20 to display the Multi Threaded Server menu.

    Show MTS Parameters (Util O/20/1)

    This utility lists the MTS parameters. To display the MTS parameters, enter option 1 from Multi Threaded Server menu. For example:
    EXL_DBA/EXL_DBA
    SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 11 16:45:09 2009
    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
    idle> Connected.
    idle> idle> idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Enter CR to continue...
     
     

    Show Listener Services (Util O/20/2)

    This utility lists the MTS dispatchers per instance. To display the MTS dispatchers, enter option 2 from the Multi Threaded Server menu. For example:

               MTS Dispatchers by Instance
               ===========================

    Service "dps" has 1 instance(s).
      Instance "dps", status UNKNOWN, has 1 handler(s) for this service...
        Handler(s):
    Service "dps.il-dtldev08c.corp.exlibrisgroup.com" has 1 instance(s).
      Instance "dps", status READY, has 1 handler(s) for this service...
        Handler(s):
    Enter CR to continue... 

     
    • Was this article helpful?