-
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
-
table name is wrong - dba_segments, not dba_segmnets - in the last "from"
-
Sorry abou that
But still it seems to hang
-
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.
-
what is file$
I am not specifying any names here but the query has to pick the names up from NAME column already in table_details .
regards
-
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.
-
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
-
Let me explain what I am doing
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
-
Correlated updates can be a pain. This link may help.
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|