Database Utilities

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Oracle Server (Util O/1)

The Oracle Server Utilty menu allows you to manage the Oracle server.

To access the Oracle Server menu:

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.
Enter the following commands to display the Managing Oracle menu.

util o
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:

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:
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:

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]
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).
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
-----------------
Exit Procedure
Activate Oracle Listener
Close Oracle Listener
Show Running Oracle Listener
Show Listener Status
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Enter option 1 from the Oracle Listener menu.

The following prompt displays:

To continue you will need to enter Oracle's password.

Password:
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:

Enter option 2 from the Oracle Listener menu.

The following prompt displays:

Do you want to restart Oracle Listener after closing? yes/[no]
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).
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

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

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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

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.
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Stop Rosetta using the startup scripts described in Starting and Stopping the System.
From the Archiving menu, enter option 1 to turn on archiving.
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:
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:

Stop Rosetta processes using the startup script, as described in Starting and Stopping the System.
From the Archiving menu, enter option 2 to turn off archiving.
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:
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Enter option 2 from the Database Users menu.
At the following prompt, enter the new user name:

Enter User Name to Create New User:
At the following prompt, enter yes to continue:

enter yes to create oracle user <new_user/>
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
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Enter option 2 from the Database Files menu.
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:
At the following prompt, enter the name of the tablespace:

Enter Tablespace name:
At the following prompt, enter the file name (including the full path) that you want to resize:

Enter file name to resize:
At the following prompt, enter the new file size:

Enter new file size (MB):
At the following prompt, enter y to continue:

confirm (y/[n]):
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:

Enter option 3 from the Database Files menu.
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:
At the following prompt, enter the name of the tablespace:

Enter Tablespace name:
At the following prompt, enter the new file name (including the full path):

Enter new file name:
At the following prompt, enter the file size:

Enter file size (MB):
At the following prompt, enter y to continue:

confirm (y/[n]):
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:

Enter option 5 from the Database Files menu.
At the following prompt, enter the name of the tablespace:

Tablespace Name:
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).
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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

Enter option 2 from the Database Free/Used Space menu
At the following prompt, enter the tablespace name:

Enter Tablespace name:
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:

Enter option 3 from the Database Free/Used Space menu.
At the following prompt, enter the tablespace name:

Enter Tablespace name:
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]:
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:

Enter option 6 from the Database Free/Used Space menu.
At the following prompt, enter the EXL_DBA user name and password:

To Coalesce Tablespaces enter EXL_DBA username/password.

username/password:
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:

Enter option 9 from the Database Free/Used Space menu.
At the following prompt, enter the name of the temporary tablespace:

To Free Temporary Segments Enter Temporary Tablespace:
At the following prompt, enter the EXL_DBA/EXL_DBA user name and password:

Enter EXL_DBA username/password:
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Enter option 1 from the Database Tablespaces menu.
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:
At the following prompt, enter the name of the new tablespace:

Enter Tablespace name:
At the following prompt, enter the new file name (include full path):

Enter new file name (full path) :
At the following prompt, enter the size of the file:

Enter new file size (MB):
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]
If you have selected UNIFORM, enter the uniform size of each extent:

UNIFORM SIZE : [128K/1M/4M/128M/1920M]
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]):
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...
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...
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:

Enter option 2 from the Database Tablespaces menu.
At the following prompt, enter the name of the tablespace:

Enter Tablespace name:
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:

Enter option 5 from the Database Tablespaces menu.
At the following prompt, enter the name of the tablespace:

Enter Tablespace name :
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Enter option 2 from the Oracle Statistics menu.
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...
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:

Enter the following commands to display the Managing Oracle menu:

util o
Enter option 18 to display the Oracle Statistics menu.
Enter option 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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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:

Enter option 2 from the Shared Pool menu.
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:

Log on to the server with the dps user.
Enter the following commands to display the Managing Oracle menu.

util o
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...