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’; ==> […]

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;   In a RAC-enabled environment, you have to use […]

How to Enable or Disable the FRA?

Using a flash recovery area simplifies the ongoing administration of your database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose. How […]

How to find locks and blockers on table

I want to find oracle locked objects. How can I see lock on table ? Sometimes, « select * from dba_blockers » does’nt return anything but somebody is blocking one or several row. This SQL statement returns select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from gv$locked_object a , gv$session b, dba_objects c where b.sid = […]