Category Archives: Reports

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 in Oracle 18c/12c/11g and perhaps find the password of the connect string?

YES you can!

COLUMN Text FORMAT a2480 WORD_WRAPPED
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON head off
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);

SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,db.db_link,db.owner) Text 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;
/

 

See also:
Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package.function <a href=”http://www.oracle-scripts.net/generate-user-ddl/”>dbms_metadata.get_ddl user</a> can be used to generate the user creation DDL.

 

Author: Vincent Fenoll – Oracle DBA Montreal

Compatibility: Oracle 18c, 12c, 11g

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

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 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

REM Without use of spool, remove "REM"
REM set term on

REM With use of spool (no display on stdout)
set term off


BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/


spool ddl_schema_trigger.sql
SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'trigger_name', 'schema_name') Text FROM SYS.DUAL;
spool off

set feed on echo on head on pages 14 term on

REM *****************************************
REM New script ======> ddl_schema_trigger.sql
REM *****************************************

Other useful options you can use:

exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘STORAGE’,false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, ‘SEGMENT_ATTRIBUTES’,false);

The generate all the DDL for a schema it’s here.

See also:
Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package.function dbms_metadata.get_ddl user can be used to generate the user creation DDL.

 

Author: Vincent Fenoll

Compatibility: Oracle 18c, 12g, 11g

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