For Checking any uncommitted transaction

 

 

 

you have to look in V$TRANSACTION, where used_ublk > 0. You can see start_time.

 

 

desc v$transaction;

 Name                                                  Null?    Type

 ----------------------------------------------------- -------- ------------------------------------

 ADDR                                                           RAW(4)

 XIDUSN                                                         NUMBER

 XIDSLOT                                                        NUMBER

 XIDSQN                                                         NUMBER

 UBAFIL                                                         NUMBER

 UBABLK                                                         NUMBER

 UBASQN                                                         NUMBER

 UBAREC                                                         NUMBER

 STATUS                                                         VARCHAR2(16)

 START_TIME                                                     VARCHAR2(20)

 START_SCNB                                                     NUMBER

 START_SCNW                                                     NUMBER

 START_UEXT                                                     NUMBER

 START_UBAFIL                                                   NUMBER

 START_UBABLK                                                   NUMBER

 START_UBASQN                                                   NUMBER

 START_UBAREC                                                   NUMBER

 SES_ADDR                                                       RAW(4)

 FLAG                                                           NUMBER

 SPACE                                                          VARCHAR2(3)

 RECURSIVE                                                      VARCHAR2(3)

 NOUNDO                                                         VARCHAR2(3)

 PTX                                                            VARCHAR2(3)

 NAME                                                           VARCHAR2(256)

 PRV_XIDUSN                                                     NUMBER

 PRV_XIDSLT                                                     NUMBER

 PRV_XIDSQN                                                     NUMBER

 PTX_XIDUSN                                                     NUMBER

 PTX_XIDSLT                                                     NUMBER

 PTX_XIDSQN                                                     NUMBER

 DSCN-B                                                         NUMBER

 DSCN-W                                                         NUMBER

 USED_UBLK                                                      NUMBER

 USED_UREC                                                      NUMBER

 LOG_IO                                                         NUMBER

 PHY_IO                                                         NUMBER

 CR_GET                                                         NUMBER

 CR_CHANGE                                                      NUMBER

 

Temp files and tablespaces

FOR DBA

Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE?

Tempfiles, unlike normal datafiles are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files:

SELECT * FROM v$tempfile;

SELECT * FROM dba_temp_files;

How do I find used/free space in a TEMPORARY tablespace?

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:

SELECT tablespace_name, SUM (bytes used), SUM (bytes free)

FROM V$temp_space_header

GROUP BY tablespace_name;