SQL LIbrary

SQL

ASH

• List Distinct SQL IDs for a Given Session

• List SQL Run Dates for a Given Plan Hash Value

Automatic Storage Management (ASM)

• List ASM Disk Groups

• List ASM Disk Groups Attached to a DB

• List ASM Disk Groups and Files

Data

• Display the Time a Row was Updated

• Check the Number of Rows per Block

• Check How Much Table data has Changed Since the Last Analyze

• Find what NLS settings are in place

• Datapump Parallel Table Export

• List all Tables with Supporting Information

• List the Size and Quantity of Extents for a given Segment

• List Fragmentation in a Segment

Flashback

• List Earliest Flashback Database Time and SCN

• List Flashback Database Restore Points

• List the Flashback Logging Size

• List Flashback Log Details

• Flashback Database Commands

IO

• Show Total IO for Each Datafile

• Show the Number of Redo Log Switches per Hour

• Show Disk File Operations

• Show Historical Redo Usage

Jobs and Scheduling

• Shows Scheduler Job Credentials

• List Job Schedules

• List Running Jobs

• Scheduler Information

• Show Scheduled Windows

• List Window Info

• Shows Job Class Info

• List Job Information

• List Info for All Jobs Currently Running

Memory

• Buffer Cache Hit Ratio

• SGA Resize Operations

• Show SGA Pool Info

• Show SGA Buffer Info

• Shows Memory Target Advice

• Show Memory Allocations for Database Sessions

• Show Object Cache Information

Miscellaneous

• List Installed RDBMS Components

• RDBMS Feature Usage

• List Service Info

• List High Water Mark Stats for Database Components.

• List All Database Parameters

• List All Hidden Database Parameters

• List Database Parameter History

• List of PL/SQL Code Blocks that Contain a Specified Word.

• Check to see if a Data Pump Job is Hung

• Read Information from the Alert Log via SQL

• Read the Alert Logs via SQL on RAC

• Find the Cause of Invalid Objects

• Show the First Day of the Month

• Show the Last Day of the Month

• Show the Number of Days in this Month

• Show the Remaining Days this Month

• Show the First Day of the Year

• Show the Number of Seconds So Far Today

• Show the Number of Seconds Left Today

• Show the Start Date and End Date of the Month

• Show Number of Months Between Two Dates

• Show the Last Day of the Year

RAC

• Lists All Locked Objects for RAC

• Show Undo Information for RAC

• List Long Operations for RAC

• List Session Waits for RAC

• List Memory Allocations for RAC Sessions

• List RAC Sessions

Resource Manager

• List Active Resource Plan

• List Consumer Groups

• List Resource Plans

• List Information on Consumer Groups Usage

• List Plan Directives

RMAN

• List RMAN Jobs

Rollback, Undo & Redo

• Show Rollback Information

• Displays Rollback Stats

• Show UNDO Information

• Show REDO Information

• Display Archive Log Generation by Day

Sessions

• List Database Sessions

• Blocking Locks

• Blockers and Waiters with RowID

• Blocking Locks (All New Verbose Version)

• Shows Object Accessed for a Given Session ID

• List SQL Statements Held for a Specific Session ID

• List Session ID for a Given OS Process ID

• List Session Details for a Given Time Period

• Tracking Down an Old Transaction from SQL Statement

Sizing

• Segment Space Usage By Owner

• Shows Temporary Tablespace Usage

• Tablespace Space Usage

• View Datafile Info

• Displays Space Usage for Each Datafile

• Show the High Water Mark for Datafiles and whether they can be Resized

• Show the High Water Mark for a Given Table

• List the Number of Extents for All Indexes for a Given Table

• List Tables with More than One Extent

• Show Gaps in Tablespace or Specific Datafile

• Displays Temp Space Currently in use by Users

• Display DB Cache Advice

Statistics and Plan Control

• Check for Histograms on User Objects

• Load SQL Plans from the SQL Cache

• How to Disable Plan Directives

• How to Disable Auto Drop of Plan Directives

Tuning

• List Summed Database Waits

• List Database Waits

• Show Session Waits for a Given Session ID

• Shows Trace Filename for Current Session

• Displays SQL Statement for the Specified Address or Hash

• List the Most Resource Hungry SQL Statements

• Show Info for Latches

• Show SQL Workarea Memory Usage Statistics

• Show CPU Usage for Active Sessions

• Show the Plan Hash Values for a Given SQLID Over a Given Period

• Show the Bind Variable Value for a Given SQLID

• List ITL Waits on a Given User Objects

• List Table Scans and Fetch Statistics Since Startup

• Show Top SQL by Execution for a Given Date Time

• Show Best Performing Hash Plan Values Over Time

• Check to See if UNDO is Configured Correctly

• List Current SQL by Execution Time By Schema

• List the Rows Processed by SQLID for a Given Schema

• List Total Executions by SQLID for a Given Schema

• List SQL Stats for a Given User

• List History of an Active Session

Users, Schema & Objects

• Show the Defined Default Column Values for a Given Table

• List Users Created On The Database

• List Who is Logged On

• List Database Links

• Shows Index Usage for a Given Table

• Lists All Locked Objects

• List of Accessed Objects

• Show Info for a Given Index

• Displays Last Analyzed Details for a Given Schema

• List Unusable Indexes for a Specified Schema

• List All Temporary Segments

• List an Object and Object Type via a Given File and Block ID.

• Check for Misisng Indexes on FKs for the Current User

• List the Current Users Session Parameters

• Get the Schema Currently Connected Too

• Get the DDL for a Given Table

PL/SQL

• Check for Foreign Key Indexes

• Script to Dynamically Create Missing Foreign Key Indexes

• Check Indexes for Fragmentation

• Cretae AWR Reports

• DBMS ADVISOR: Segment Advice

• Get Historical Plans from the AWR Using SQLID

• Password Cracker Copyright: Peter Finnigan

• Online Table Reorg

• Tablespace Space Usage Procedure

• Session Tracing

• Sort Usage Procedure

• Tracing PLSQL

• How to Export and Import a Schema using DBMS_DATAPUMP - Remap Schema Version

• How to Export and Import using DBMS_DATAPUMP - DB Link Version

Published 

31st May 2022