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.