RE: How to see DDL executing

From: Daemen, Remco <R.Daemen_at_facent.nl>
Date: Fri, 13 Oct 2000 14:56:27 +0200
Message-Id: <10648.119169@fatcity.com>

You can monitor the size of the new table by looking in dba_segments for objects of type 'TEMPORARY' in the tablespace you are creating the new table in. That's all I can think of ...

Try this:

select segment_name,segment_type,bytes/(1024*1024) MB,tablespace_name,extents
from dba_segments where segment_name='OLD_TABLE' or segment_name='NEW_TABLE' UNION ALL
SELECT segment_name,segment_type,bytes/(1024*1024),tablespace_name,extents FROM DBA_SEGMENTS WHERE segment_type like 'TEMP%' and tablespace_name not like 'TEMP%';

Your output will first look like this :

(statement: create table new_table as select * from old_table)

SEGMENT_NAME                   SEGMENT_TYPE           MB TABLESPACE_NAME

EXTENTS

------------------------------ --------------- ---------
------------------------------ ---------
OLD_TABLE                      TABLE           132.34375 DATA_1
847
15.31772                       TEMPORARY       122.34375 DATA_2

783

and eventually like this:

SEGMENT_NAME                   SEGMENT_TYPE           MB TABLESPACE_NAME

EXTENTS

------------------------------ --------------- ---------
------------------------------ ---------
OLD_TABLE                      TABLE           132.34375 DATA_1
847
NEW_TABLE                      TABLE            132.8125 DATA_2

850

I don't know why the number of extents is different, though. Anybody has any ideas ?

> ----------
> From: Walter K[SMTP:alden14004_at_yahoo.com]
> Sent: vrijdag 13 oktober 2000 14:50
> To: Multiple recipients of list ORACLE-L
> Subject: How to see DDL executing
>
> Is there a place where I can see the SQL of DDL statements executing? For
> example, if I execute a statement such as CREATE TABLE..AS SELECT.. I
> cannot see the SQL in v$sqlarea or v$sqltext. I have a need to monitor the
> progress.
>
> Any suggestions and/or scripts would be MUCH appreciated.
Received on Fri Oct 13 2000 - 07:56:27 CDT