This content has been marked as final. Show 10 replies
939517 wrote:If you want data to be available to other sessions, do not use GTT.
Suppose, I am in session a,
I have created following GTT table=>
create global temporary table tt_test
Fname varchar2(20), Lname varchar2(20);
on commit preserve rows;
Inserting data into GTT=>
insert into tt_test values ('john','smith');
Commit the session=>
Now I have opened another session b, and I want to see the data of tt_test table from another session.
How I can acheive it as long as sesssion a is active?
The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.
Data of Tablespaces is calculated by some query like below: [Found it posted over another Thread in OTN itself]
If, the session querying has DBA privilege, you would be able to query it.
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name UNION select tablespace_name, sum(bytes)/1024/1024 tbs_size from dba_temp_files group by tablespace_name ) b where a.tablespace_name(+)=b.tablespace_name;
But, why do you want to check data for another session? That exactly violates the purpose of Global Temporary Table.
It is meant to store data pertaining to a Specific Transaction/Session. If you want to access the data of another session, use a Normal Table.
I believe below could be the SQL that you are looking for: It would not give all the details you seek for, but will provide you the session details that has accommodated a particular number of extents/blocks.
For greater details, read V$TEMPSEG_USAGE
select session_num from v$tempseg_usage a join v$session b on a.session_num = b.serial#;