How to convert rows to one column in SQL?

The goal is to convert all the row values in a column to a single concatenated list.
You can change the space separated by comma or other csv like fields.

Example of a result:

ID ADDRESS
id1 addres1 addres2 addres3
id2 addres4
id3 addres5 addres6
id4 addres7 addres8 addres9 addres10 addres11

select ID,
max(decode(my_seq,1,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,2,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,3,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,4,ADDRESS)) || ‘ ‘ ||
max(decode(my_seq,5,ADDRESS)) emp_list
from (
select ID,
ADDRESS,
row_number() over
(partition by ID order by ADDRESS) my_seq
from customer_table
)
WHERE ADDRESS IS NOT NULL
group by ID
order by 1

Author: Vincent Fenoll Oracle DBA Montreal
compatibility: Oracle 10g 11g 12c

How to generate DDL for db_links

Is there anyway to extract ddl for all database links and perhaps find the password of the connect string?
YES

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,db.db_link,db.owner) from dba_db_links db

Add a Where clause for a specific DDL:
where upper(db_link) like ‘%example%’;

Next, look at your TNS_ADMIN directory (or tnsnping the alias) to find what is the target.

If your DB is <= 11.2.0.2, you can crack the password of the dblink using this method: select passwordx from sys.link$ where name='example; PASSWORDX -------------------------------------------------- 05CA78F36B77C902B9286FFC981F4C9A92F8479D406ADWA670 set serveroutput on declare db_link_password varchar2(200); begin db_link_password := '05CA78F36B77C902B9286FFC981F4C9A92F8479D406ADWA670'; dbms_output.put_line ('Password: ' || utl_raw.cast_to_varchar2 ( dbms_crypto.decrypt ( substr (db_link_password, 19) , dbms_crypto.DES_CBC_PKCS5 , substr (db_link_password, 3, 16) ) ) ); end; /

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;
    
      EXIT WHEN c_my_table%NOTFOUND;
      begin
        sql_stmt := 'select count(*) from my_table where ' ||
                    ch_column_name || '=my_value_or_string';
      
        execute immediate sql_stmt
          into n_count;
        dbms_output.put_Line(ch_column_name || ',' || n_count);
      
      exception
        when others then
          dbms_output.put_Line(ch_column_name || ',' || 'N/A');
        
      end;
    
    END LOOP;
    CLOSE c_my_table;
  
  END;
END;
/

Author: Vincent Fenoll, Oracle DBA Montreal

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 "Year-Month",
count(*) as "Month log switch count"
FROM V$log_history
GROUP BY to_char(first_time, 'yyyy-MM')
ORDER BY 1;

If we want to know the Oracle log switch number day by day and hour by hour, ths script does the job:

set lines 130; 
set pages 999; 
SELECT 
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
GROUP BY TO_CHAR (first_time, 'YYYY-MON-DD')
ORDER BY 1;

Author: Vincent Fenoll (Montreal)
Compatible: Oracle 7 to 12c

Database Health Check (DBMS_HM)

The Oracle DBA wants to run Health Checks with Health Monitor upon his database in Montreal (or elsewhere ;) ).

With Oracle 11g, these checks can be done:
– DB Structure Integrity Check
– CF Block Integrity Check
– Data Block Integrity Check
– Redo Integrity Check
– Transaction Integrity Check
– Undo Segment Integrity Check
– Dictionary Integrity Check
– ASM Allocation Check

Viewing the list of checks that can be done on your database:

 SELECT name
  FROM v$hm_check
 WHERE internal_check = 'N';

Health checks accept input parameters, some are mandatory while others are optional.

Displaying parameter information for all health checks:

  SELECT c.name check_name,
         p.name parameter_name,
         p.TYPE,
         p.DEFAULT_VALUE,
         p.description
    FROM v$hm_check_param p, v$hm_check c
   WHERE p.check_id = c.id AND c.internal_check = 'N'
  ORDER BY c.name;

Running a check:

BEGIN
   DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');
END;
/

or

BEGIN
   DBMS_HM.RUN_CHECK (check_name     => 'Transaction Integrity Check',
                      run_name       => 'my_transaction_run',
                      input_params   => 'TXN_ID=22.87.1');
END;

Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;

Listing all the Health Check executed (Health Monitor View):

SELECT run_id,
       name,
       check_name,
       run_mode,
       status,
       src_incident,
       num_incident,
       error_number
  FROM v$hm_run;

Vincent Fenoll – Oracle OCP Database administrator in Montreal