Oracle: RecycleBin

From 10g, When you drop a table, Oracle renames the table and stores it in logical recycle bin. The table structure is preserved, meaning all the triggers and indexes on the table are preserved. Even though the table is logically dropped, it still exists in bin, there by occupying space in the tablespace

To view the contents of the recyclebin, go to sqlplusw, and type

sql> show recyclebin

If you dropped the table by mistake and want to revert it back, then issue the command

sql> Flashback table table_name to before drop;

If you want to permanently drop a table,bypassing recyclebin then

sql> drop table table_name purge;

To clear Recylcebin

sql> purge recyclebin

Scripts to manage recyclebin

To drop a table from recycle bin

purge table table_name

You can also purge tables at tablespace level

Purge tablespace tablespace_name

Purge for specific users in tablespace

Purge tablespace tablespace_name USER user_name

To drop all objects in recyclein

purge dba_recylebin

Oracle: How to kill Jobs

Sometimes Jobs run in background and they may not be visible in session viewer

Use below SQL to kill jobs

To get list of jobs running

Select sid,d.*
from dba_jobs_running r,dba_jobs d
where r.job = d.job

For SID from above query, Get Serial# from v$ v$session

select * from v$session
where sid in 138

ALTER SYSTEM KILL SESSION '138,19296';

Alter session kill session 'SID,SERIAL#'

Oracle: Renaming Tablespace

From 10g, it is possible to rename tablespace (except SYSTEM and SYSAUX).

Alter Tablespace old_name rename to new_name

Example:

Alter tablespace BB_Data rename to ZZ_Data

Renaming UNDO Tablespace

Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS1

Alter Tablespace UNDOTBS1 Rename to UNDOTBS2

Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS2

Oracle: Renaming Tablespace

From 10g, it is possible to rename tablespace (except SYSTEM and SYSAUX).

Alter Tablespace old_name rename to new_name

Example:

Alter tablespace BB_Data rename to ZZ_Data

Renaming UNDO Tablespace

Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS1

Alter Tablespace UNDOTBS1 Rename to UNDOTBS2

Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS2

Oracle:How to calculate Rollback duration

Here is a query to figure out how long it will take for a rollback to complete.

Select username,opname,start_time,last_update_time,totalwork,units,sofar,
elapsed_seconds,time_remaining,sql_Text
from v$session_longops l, v$sql s
where l.sql_id = s.sql_id(+)
and sid = sid_of_the_session_doing_rollback

Above query takes SID as input parameter.Replace "sid_of_the_session_doing_rollback" with the SID

For a Rollback Operation

Opname will say "Transaction Rollback"
Total Work: Amount of work.
Units: Total work is mesaured in terms of UNITS columns
SoFar: Work done so far.
SQL_TEXT: Sql that is being rolled back.

Since this sql queries LONGOPS View, you can use it to get an idea for how long any process would take.

Oracle: Flashback versions Query

Oracle 10g has built in audit management to track changes to the table. Oracle tracks the values by using pseudo-columns versions_starttime,versions_endtime,
versions_xid ,versions_operation,versions_startscn and versions_endscn

versions_xid: Column identifies transaction that modified the table.You can query FLASHBACK_TRANSACTION_QUERY table for this value to get the history of the transaction and the corresponding UNDOSQL

Example

Create table testing (col1 varchar2(10), col2 int);

Insert into testing values ( 'A',10);
Insert into testing values ( 'B',20);
Commit;

Update testing set col2 = 202 where col1 = 'B';
Commit;

Delete from testing ;
Commit;

sELECT * from testing; -->0 Records

Now to get the history

Select testing.*,versions_starttime,versions_endtime,versions_operation,
versions_startscn,versions_endscn,versions_xid
from testing
versions between timestamp minvalue and maxvalue

To get details of the transaction and the undo sql fo the transaction,

Select * from
flashback_transaction_query
where xid ='050054001FAF0000'

IMPORTANT
This query pulls data from undo segments, so you cannot get a complete history of the transaction.As long as you have the data in UNDO_SEGMENTS, You can always query history of any tables.