top.sql- Configuring an Efficient Environment

The following script lists the top CPU-consuming SQL processes. It’s useful for identifying problem SQL statements. Place this script in a directory such as HOME/scripts:

This is how you execute this script:

Here is a snippet of the output, showing a SQL statement that is consuming a large amount of database resources:

lock.sql

This script displays sessions that have locks on tables that are preventing other sessions from completing work. The script shows details about the blocking and waiting sessions.

You should place this script in a directory such as HOME/scripts. Here are the contents of lock.sql:

The lock.sql script is useful for determining what session has a lock on an object and also for showing the blocked session. You can run this script from SQL*Plus, as follows:

When running lock.sql from the root container, you’ll need to change DBA_OBJECTS to CDB_OBJECTS for the script to properly report locks throughout the entire database. You should also consider adding the NAME and CON_ID to the query so that you can view the container in which the lock is occurring. Here’s a snippet of the modified query (you’ll need to replace the … with columns you want to report on):

users.sql

This script displays information about when users were created and whether their account is locked. The script is useful when you’re troubleshooting connectivity issues. Place the script in a directory such as HOME/scripts. Here is a typical users.sql script for displaying user account information:

You can execute this script from SQL*Plus, as follows:

Here is some sample output:

Now with 23c you will need to update users.sql for the multitenant database environment to get the users in the container and pluggable databases and run from the root container. Change DBA_USERS to CDB_USERS and add the NAME and CON_ID columns to report on all users in all pluggable databases.