About Vincent

Oracle DBA OCP 10g, 9i, 8 Electronic music producer

How to flashback a procedure, function or package?

If you accidentally dropped or modified a procedure, function or package. You can rollback to another version in a time.
If Flashback is disabled on your database, the rollback time has to be short in time because you will use your undo tablespace

Find the object:
select object_id from dba_objects
where object_name=’MY_OBJECT_NAME’ and owner=’OWNER_OF_THE_OBJECT’;
==> 1010952

Flashback table:
select SOURCE from sys.source$ as of timestamp
to_timestamp(’31-Mar-2017 10:00:52′,’DD-Mon-YYYY hh24:MI:SS’)
where obj#=1010952 ;

List of active transaction per users

If I have a lock problem (for example a select for update not committed), I can start with the list of active transactions per users:

select s.sid
      ,s.serial#
      ,s.username
      ,s.machine
      ,s.status
      ,s.lockwait
      ,t.used_ublk
      ,t.used_urec
      ,t.start_time
from gv$transaction t
inner join gv$session s on t.addr = s.taddr;

How to cleanup orphaned datapump jobs

How can I cleanup old (orphaned) datapump jobs?

Identify these jobs:

SET lines 150
COL owner_name FORMAT a10
COL job_name FORMAT a20
COL operation FORMAT a10

SELECT owner_name, job_name, operation
FROM dba_datapump_jobs where state='NOT RUNNING' and attached_sessions=0;

 

Drop the master tables:

set head off
SELECT 'drop table ' || owner_name || '.' || job_name || ';'
FROM dba_datapump_jobs WHERE state='NOT RUNNING' and attached_sessions=0;

Execute the generated script.

 
If using recycling bin:
SELECT ‘purge table ‘ || owner_name || ‘.’ || ‘”‘ || job_name || ‘”;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and attached_sessions=0;

 

Author: Vincent Fenoll – Oracle DBA from Montreal

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; /