is there a better way of writing this SQL

[puneet]

UPDATE TABLE_DETAILS SET EXTENTS = (SELECT EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (SELECT DISTINCT(SEGMENT_NAME) FROM DBA_SEGMNETS WHERE SEGMENT_NAME =NAME ))


I want to populate the table TABLE_DETAILS column extents with the number of extents for DBA segments .Distinct is used as there are more than 1 segment for some tables .


This SQL seems to hang

regards

[stecal]

table name is wrong - dba_segments, not dba_segmnets - in the last "from"

[puneet]

Sorry abou that

But still it seems to hang

[Shiva]

It works for me :

UPDATE TABLE_DETAILS SET EXTENT = (SELECT EXTENTS FROM DBA_SEGMENTS WHERE
SEGMENT_NAME IN ('FILE$' )) ;

0 rows updated.

Elapsed: 00:00:00.00

Why are you using 2 nested queries for the same purpose ?

Shiva.

[puneet]

I am not specifying any names here but the query has to pick the names up from NAME column already in table_details .

regards

[Shiva]

I guess it is because of 'NAME' being a reseved word. This query does not hang :

UPDATE TABLE_DETAILS td SET td.EXTENT = (SELECT EXTENTS FROM DBA_SEGMENTS WHERE
SEGMENT_NAME IN (SELECT DISTINCT(SEGMENT_NAME) FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = td.name));

Shiva.

[stecal]

You could try this:

create table extent_details as
select count(segment_name) seg_count, sum(extents) sum_ext, segment_name
from dba_segments
group by segment_name
order by 2 desc

SQL> select * from extent_details;


SEG_COUNT SUM_EXT SEGMENT_NAME
---------- ---------- ---------------
1 79 SOURCE$
1 39 I_SOURCE1
1 28 SYSROL
1 22 IDL_UB1$
1 21 IDL_UB2$
12 12 CARS
1 12 VIEW$
1 9 ARGUMENT$
1 9 C_OBJ#
1 8 I_COL1
1 8 SYSTEM

[puneet]

create table Table_details (Name varchar2(81), num_Rows Number(10), extents number(10))

insert into Table_details (Name ,num_Rows) select DISTINCT(table_name) ,num_rows from dba_tables

Just want to populate extents column from dba_segments for those particular tables

Thanks !!!


UPDATE TABLE_DETAILS td SET td.EXTENTS = (SELECT EXTENTS FROM DBA_SEGMENTS WHERE
SEGMENT_NAME IN (SELECT DISTINCT(SEGMENT_NAME) FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = td.name));

Has been running for > 5 min for 300 rows . don't expect iot to stop . please let me know for a better SQL

[Heath]

Correlated updates can be a pain. This link may help.

[ronnie]

Originally posted by Heath
Correlated updates can be a pain. This link may help.

which link ??????????

maybe this one...

http://asktom.oracle.com/pls/ask/f?p...A:273215737113