BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_RECYCLEBIN view tips

Oracle Tips by Burleson Consulting

DBA_RECYCLEBIN

<< Return to the index

Oracle 11g makes the following comments about the DBA_RECYCLEBIN table: "Description of the Recyclebin view accessible to the user"

Related notes on DBA_RECYCLEBIN:

Column description of the DBA_RECYCLEBIN view:

OWNER

Description of DBA_RECYCLEBIN.OWNER: "Name of the original owner of the object"

OBJECT_NAME

Description of DBA_RECYCLEBIN.OBJECT_NAME: "New name of the object"

ORIGINAL_NAME

Description of DBA_RECYCLEBIN.ORIGINAL_NAME: "Original name of the object"

OPERATION

Description of DBA_RECYCLEBIN.OPERATION: "Operation carried out on the object"

TYPE

Description of DBA_RECYCLEBIN.TYPE: "Type of the object"

TS_NAME

Description of DBA_RECYCLEBIN.TS_NAME: "Tablespace Name to which object belongs"

CREATETIME

Description of DBA_RECYCLEBIN.CREATETIME: "Timestamp for the creating of the object"

DROPTIME

Description of DBA_RECYCLEBIN.DROPTIME: "Timestamp for the dropping of the object"

DROPSCN

Description of DBA_RECYCLEBIN.DROPSCN: "SCN of the transaction which moved object to Recycle Bin"

PARTITION_NAME

Description of DBA_RECYCLEBIN.PARTITION_NAME: "Partition Name which was dropped"

CAN_UNDROP

Description of DBA_RECYCLEBIN.CAN_UNDROP: "User can undrop this object"

CAN_PURGE

Description of DBA_RECYCLEBIN.CAN_PURGE: "User can purge this object"

RELATED

Description of DBA_RECYCLEBIN.RELATED: "Parent objects Obj#"

BASE_OBJECT

Description of DBA_RECYCLEBIN.BASE_OBJECT: "Base objects Obj#"

PURGE_OBJECT

Description of DBA_RECYCLEBIN.PURGE_OBJECT: "Obj# for object which gets purged"

SPACE

Description of DBA_RECYCLEBIN.SPACE: "Number of blocks used by this object"

DBA_RECYCLEBIN View Source

Oracle 11g's data dictionary defines the DBA_RECYCLEBIN view using the following source query:

select u.name, o.name, r.original_name,
decode(r.operation, 0, 'DROP', 1, 'TRUNCATE', 'UNDEFINED'),
decode(r.type#, 1, 'TABLE', 2, 'INDEX', 3, 'INDEX',
4, 'NESTED TABLE', 5, 'LOB', 6, 'LOB INDEX',
7, 'DOMAIN INDEX', 8, 'IOT TOP INDEX',
9, 'IOT OVERFLOW SEGMENT', 10, 'IOT MAPPING TABLE',
11, 'TRIGGER', 12, 'CONSTRAINT', 13, 'Table Partition',
14, 'Table Composite Partition', 15, 'Index Partition',
16, 'Index Composite Partition', 17, 'LOB Partition',
18, 'LOB Composite Partition',
'UNDEFINED'),
t.name,
to_char(o.ctime, 'YYYY-MM-DD:HH24:MI:SS'),
to_char(r.droptime, 'YYYY-MM-DD:HH24:MI:SS'),
r.dropscn, r.partition_name,
decode(bitand(r.flags, 4), 0, 'NO', 4, 'YES', 'NO'),
decode(bitand(r.flags, 2), 0, 'NO', 2, 'YES', 'NO'),
r.related, r.bo, r.purgeobj, r.space
from sys."_CURRENT_EDITION_OBJ" o, sys.recyclebin$ r, sys.user$ u, sys.ts$ t
where o.obj# = r.obj#
and r.owner# = u.user#
and r.ts# = t.ts#(+)

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books