Scripts order by Category

      Database:

Alertlog Tail AWR Info Blocking Sessions Blocking Sessions1 DB Links DBA Directories DBA Directories Upd High Water Mark GSS Run Locked Objects Tablespace Free Database Info Database Consistency Resetlogs Time RMAN Sofar RMAN Sofar1 RMAN Status Sync Status on Prim Sync Status on DR User Undo Utl File Dir Wait Events DBA Files All DBA Tablespaces DML Locks DML Locks1 Long Operations Long Operations1 Resource Limit Role Privs DBMS Jobs Table Size Tablespace Usage Temp Extent Mapping Temp Free Temp Free1 Temp IO Temp Segments Temp Usage Temp Seg Usage Top CPU Tablespace Free TS Info Health of DB Temp Usage Invalids Jobs Total Jobs Running Max Extents PID Redo Per Hour Sessions Sessions1 Sessions User Sessions BG Sessions by CPU Sessions by Undo Sessions by Undo1 Sid Sid1 Undo UndoStatus User hit ratio User Privs User Temp Space

Concurrent Request (CR):

CR Details from Request ID CR Duration CRs Running Currently CRs Running Currently1

CR Pedning CR Pednig1 CR Pending on CRM CR Pending on CRM1 CR Pending on Internal Manager CR Pending on Stdmgr

CR Error in 24Hrs CRs Errorred Scheduled CRs

Oracle Applications Scripts:

cmclean

Oracle Applications Scripts

Description,Explanation, Execution and Sample Output	Download Script
Oracle Application Products Version	product_status.sql
Applications Relese/Version	release.sql
Patch applied or not?	patch.sql

Locks/Blocking Session Scripts

Description,Explanation, Execution and Sample Output	Download Script
Finding Blocking sessions	locks.sql
Objects currently Locked	locked_obj.sql

User Management Scripts

Description,Explanation, Execution and Sample Output	Download Script
Privilege associated with a User	user_privs.sql

Session Management Scripts

Description,Explanation, Execution and Sample Output	Download Script
Sql text running that is running by a Session with an SID	sql_text.sql
Session information from Process ID	pid.sql
Sessions currently connected to Database	sess_runnig.sql
Session information from Session ID (SID)	sid.sql

Database Info Scripts

Description,Explanation, Execution and Sample Output	Download Script
Database Information	db.sql
Check invalid objects	invalids.sql
Particular Table Size	tab_size.sql

Dataguard/Standby Info Scripts

Description,Explanation, Execution and Sample Output	Download Script
Sync status query to be used on Standby Database	sync_stdby.sql
Sync status query to be used on Primary Database	sync.sql

Tablespace/Datafiles Scripts

Description,Explanation, Execution and Sample Output	Download Script
Tablespaces Information	tsinfo.sql
Tablespace Free Space	ts_free.sql

RMAN Info Scripts

Description,Explanation, Execution and Sample Output	Download Script
RMAN Progress Sofar	rman_sofar.sql
RMAN backup information	rman_bkp.sql
RMAN Backup Status	rman_status.sql

Concurrent Request Scripts

Description,Explanation, Execution and Sample Output	Download Script
Concurrent Manager Status from Back-end	cm_status.sql
Concurrent Request information from Request ID	cr_info.sql
Concurrent Requests Currently Running	crs.sql
Pending Concurrent Requests Count	pend_crs_cnt.sql
Pending Concurrent Requests Count with Conflict Resolution Manager(CRM)	pend_crs_cnt_crm.sql
Pending Concurrent Requests Count with Standard Manager	pend_crs_cnt_stdm.sql
Pending Concurrent Requests Information with all the Managers	pend_cr.sql
Concurrent Requests Information Pending with Conlict Resolution Manager	pend_crs_crm.sql
Concurrent Requests Information Pending with Standard Manager	pend_crs_stdm.sql

Actual Objects Size in DB

Description	Actual size occupied by objects
Download Script	db_obj_size.sql
Date	20-Sep-2013

In this example we can find the actual size of the objects i.e., the space occupied by all the physical objects not the space occupied by datafiles.

The objects include the below list

• LOBINDEX
• INDEX PARTITION
• TABLE SUBPARTITION
• ROLLBACK
• TABLE PARTITION
• NESTED TABLE
• LOB PARTITION
• INDEX SUBPARTITION
• CACHE
• LOBSEGMENT
• INDEX
• TABLE
• TYPE2 UNDO
• CLUSTER
• LOB SUBPARTITION

SELECT sum(bytes)/1024/1024 "Used in MB" FROM dba_segments
/

Size occupied by each type of object

select unique segment_type,sum(bytes)/1024/1024 from dba_segments group by segment_type
/

select unique segment_type,sum(bytes)/1024/1024 "Size in MB"from dba_segments group by segment_type /

Sample Execution and Output

SQL> SELECT sum(bytes)/1024/1024 "Size in MB" FROM dba_segments
/
Size in MB
----------
435925.375

SQL> select unique segment_type,sum(bytes)/1024/1024 "Size in MB"from dba_segments group by segment_type
/

SEGMENT_TYPE                 Size in MB
------------------ --------------------
LOBINDEX                      1513.6875
INDEX PARTITION              24582.5625
TABLE SUBPARTITION                  136
ROLLBACK                           .375
TABLE PARTITION              24811.8125
NESTED TABLE                      4.375
LOB PARTITION                   12.5625
INDEX SUBPARTITION                   83
CACHE                             .0625
LOBSEGMENT                   56456.3125
INDEX                        136582.688
TABLE                            186685
TYPE2 UNDO                    4482.5625
CLUSTER                         310.375
LOB SUBPARTITION                    269

15 rows selected.