For each column, count a value

For each column of the table « my_table », I want to count how many rows containing « my_value_or_string » there are in the table. In the script, change « my_table » and « my_value_or_string ». BEGIN declare cursor c_my_table is select COLUMN_NAME from dba_tab_columns where TABLE_NAME = ‘my_table’; ch_column_name dba_tab_columns.COLUMN_NAME%TYPE; n_count number(20); sql_stmt VARCHAR2(200); BEGIN open c_my_table; LOOP FETCH c_my_table INTO ch_column_name; […]

Generate and format trigger DDL

How to format DBMS_METADATA.GET_DDL? To get a formatted definition of your DDLs, with all the semicolons and avoid lines break (Wrapped lines). Here is an example to retrieve the DDL of a triggers using the DBMS_METADATA package. For Oracle 18c / 12c / 11g / 10g: COLUMN Text FORMAT a2480 WORD_WRAPPED SET LONG 20000 LONGCHUNKSIZE […]

Log switch number

The Oracle DBA wants to know how many log switch the database made a particular month (for example 2015 of august) SELECT to_char(first_time, ‘yyyy-MM’) as « Year-Month », count(*) as « Month log switch count » FROM V$log_history GROUP BY to_char(first_time, ‘yyyy-MM’) having to_char(first_time, ‘yyyy-MM’)=’2015-08′ To obtain all the months, just remove the HAVING clause: SELECT to_char(first_time, ‘yyyy-MM’) as […]

Lob Space usage

How the Oracle DBA can find the size of a LOB segment, the space that is actually allocated to the LOB data and the unused space within the LOB segment, above the HWM. — ################################################ — # Creator: Vincent Fenoll — # Created: 2011/09/14 — # Name: LOBSpace.sql — ################################################ — # — # Compatible: […]

ZFSFreeMem

— ################################################ — # Creator: Cyrille Modiano — # Created: 2011/24/02 — # Name: ZFSFreeMem — ################################################ — # — # Compatible: Solaris 10 with ZFS cache — # — ################################################ — # — # This script returns the free memory size and his — # percentage on a solaris 10 host with ZFS cache […]