Hi Tom,

In a customer project we are using quite a lot of "create table as select ...." (cannot change them), that are taking quite a while (some hours).
I would like to observe the growth of these objects, while they are created, but I'm bumping into an issue.

Example:

(tall_objects is a plain "create table tall_objects as select * from all_abjects")

This statement takes a few minutes (a nonsens long running Statement just for demonstrating the problem):

create table o1 as
select * from (
select a.owner,a.object_name,b.last_ddl_time
from tall_objects a, tall_objects b
where b.object_type = 'SYNONYM'
and a.object_type = 'SYNONYM'
) where rownum < 100000000;

from another session I can issue:

SQL> select uss.segment_name Table_Name, bytes/1024/1024 MByte from user_segments uss
2 where uss.segment_name in (
3 select * from (
4 select us.segment_name from user_segments us
5 minus
6 select uo.object_name from user_objects uo
7 )
8 where segment_name not like 'SYS%'
9 and segment_name not like 'BIN%')
10 ;

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 2946

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 3138

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 3202


SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 3970

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 4098

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------
4.106770 4802

SQL> /

TABLE_NAME MBYTE
-------------------------------------------------------------------------------- ----------

SQL>
(statement done)

Ah, nice! one can observe the growth of the object "o1" during creation
Q: Do you have any idea to get rid of this ugly "4.106770" but display an "o1" instead (the object beeing created)?

Thx in advance!

SQL> select
  2    uss.segment_name Table_Name,
  3    segment_type,
  4    bytes/1024/1024 MByte
  5  from user_segments uss;

TABLE_NAME                     SEGMENT_TYPE            MBYTE
------------------------------ ------------------ ----------
6.41434                        TEMPORARY                   0

SQL> select child_name, sql_fulltext from v$sql where sql_id = '3fpqv6mbh93n6';

CHILD_NUMBER SQL_FULLTEXT
------------ -------------------------------------------------
           0 create table t2 as
             select * from (
             select a.owner,a.object_name,b.last_ddl_time
             from t1 a, t1 b
             where b.object_type = 'SYNONYM'
             and a.object_type = 'SYNONYM'
             ) where rownum < 100000000

SQL> select elapsed_time/1000000, rows_processed, PHYSICAL_WRITE_BYTES
  2  from v$sql
  3  where sql_id = '3fpqv6mbh93n6';

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           23.665873              0           1665826816

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           23.665873              0           1665826816

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           25.665924              0           1803059200

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           27.665987              0           1997897728

SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           29.666028              0           2126741504

SQL>
SQL> /

ELAPSED_TIME/1000000 ROWS_PROCESSED PHYSICAL_WRITE_BYTES
-------------------- -------------- --------------------
           65.648563       99999999           5196685312