Master-Detail

[krot]

I need functionality "Master-Detail".
Developer needs.
Is it possible to add this functionality in the future?

 

[Marco Kalter]

You mean a master-detail screen for editing table data?

 

[JKLI]

As Master-Detail you can currently use the Session tool where you can define own session query (Filter tab) (for example Tablespaces swith some master data ) and then on the Detail tab you can define the queries for detail data (for example tablespace segments).

It would be nice if this Master-Detail window could be as an independend object which can be saved. Also if we could configure a tool (invoked from the context menu on right mouse click to perform an action based with ability to pass data from cell where you click) we can customize PL/SQL Developer for many other task (DBA, Developer ...)

My example for session tool:
Filter :
Select total.ts tablespace,
DECODE(total.mb, Null, 'OFFLINE', dbat.status) status,
total.mb "Curr total MB",
total.MaxMb "Max Total MB",
NVL(total.mb - free.mb, total.mb) "Used MB",
NVL(free.mb, 0) "Curr Free_MB",
Case
When (total.mb Is Null) Then
'[' || RPAD(LPAD('OFFLINE', 13, '-'), 20, '-') || ']'
Else
'[' || DECODE(free.mb,
Null,
'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X', trunc((100 - ROUND((free.mb+(total.MaxMb-total.mb)) / (total.MaxMb) * 100, 2)) / 5), 'X'), 20, '-'),
'--------------------')) || '] ' || to_char(DECODE(total.MaxMb, Null, 0, NVL(ROUND((total.mb - free.mb) / (total.MaxMb) * 100, 2), 100))) || '%'
End As "Graph %Max Size Used",
Case
When (total.mb Is Null) Then
'[' || RPAD(LPAD('OFFLINE', 13, '-'), 20, '-') || ']'
Else
'[' || DECODE(free.mb,
Null,
'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X', trunc((100 - ROUND((free.mb) / (total.mb) * 100, 2)) / 5), 'X'), 20, '-'),
'--------------------')) || '] ' || to_char(DECODE(total.mb, Null, 0, NVL(ROUND((total.mb - free.mb) / (total.mb) * 100, 2), 100))) || '%'
End As "Graph %Current Size Used",
Total.AutoExtensible AutoExtensible,
dbat.contents,
dbat.extent_management,
dbat.segment_space_management,
dbat.logging
From (Select tablespace_name ts,
Max(autoextensible) AutoExtensible,
Sum(bytes) / 1024 / 1024 mb,
Case
When Sum(maxbytes)=0 Then Sum(bytes) / 1024 / 1024
Else Sum(maxbytes)/ 1024 / 1024
End MaxMb
From dba_data_files
Group By tablespace_name) total,
(Select tablespace_name ts,
Sum(bytes) / 1024 / 1024 mb
From dba_free_space
Group By tablespace_name) free,
dba_tablespaces dbat
Where total.ts = free.ts(+)
And total.ts = dbat.tablespace_name
Union All
Select sh.tablespace_name,
'TEMP',
Sum(sh.bytes_used + sh.bytes_free) / 1024 / 1024,
Null,
Sum(sh.bytes_used) / 1024 / 1024,
Sum(sh.bytes_free) / 1024 / 1024,
Null,
'[' ||
DECODE(Sum(sh.bytes_free),
0,
'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X', (TRUNC(ROUND((Sum(sh.bytes_used) / Sum(sh.bytes_used + sh.bytes_free)) * 100, 2) / 5)), 'X'),
20,
'-'),
'--------------------')) || ']',
Null,
'TEMPORARY',
Null,
Null,
Null
From v$temp_space_header sh
Group By tablespace_name
Order By 2 Asc,
7 Desc,
5 Asc

Detail:
Select s.segment_type "Type",
s.segment_name "Name",
round(bytes / 1024 / 1024, 1) MBytes,
Case s.segment_type
When 'INDEX' Then i.num_rows
When 'TABLE' Then t.num_rows
End "Rows",
Case s.segment_type
When 'INDEX' Then i.last_analyzed
When 'TABLE' Then t.last_analyzed
End "LastAnalyzed",
Case s.segment_type
When 'INDEX' Then i.blevel
When 'TABLE' Then t.chain_cnt
End "ChainCount/BLevel",
Case s.segment_type
When 'INDEX' Then i.table_name
End "Table",
s.owner,
s.blocks,
s.Extents,
i.status
From dba_segments s, dba_indexes i , dba_tables t
Where s.tablespace_name = :tablespace And i.owner (+) = s.owner And i.index_name (+) = s.segment_name
And t.owner (+) = s.owner And t.table_name (+) = s.segment_name
Order By MBytes Desc

 

[Theod]

Hi,

I've just played with your query - using the detail as an column

WITH det AS
 (SELECT s.segment_type "Type"
		 ,s.segment_name "Name"
		 ,round(bytes / 1024 / 1024, 1) MBytes
		 ,CASE s.segment_type
			 WHEN 'INDEX' THEN
			  i.num_rows
			 WHEN 'TABLE' THEN
			  t.num_rows
		 END "Rows"
		 ,CASE s.segment_type
			 WHEN 'INDEX' THEN
			  i.last_analyzed
			 WHEN 'TABLE' THEN
			  t.last_analyzed
		 END "LastAnalyzed"
		 ,CASE s.segment_type
			 WHEN 'INDEX' THEN
			  i.blevel
			 WHEN 'TABLE' THEN
			  t.chain_cnt
		 END "ChainCount/BLevel"
		 ,CASE s.segment_type
			 WHEN 'INDEX' THEN
			  i.table_name
		 END "Table"
		 ,s.owner
		 ,s.blocks
		 ,s.Extents
		 ,i.status
		 ,s.tablespace_name ts
  FROM   dba_segments s
		,dba_indexes  i
		,dba_tables   t
  WHERE  1 = 1
  AND    i.owner(+) = s.owner
  AND    i.index_name(+) = s.segment_name
  AND    t.owner(+) = s.owner
  AND    t.table_name(+) = s.segment_name
  ORDER  BY MBytes DESC),
mst AS
 (SELECT total.ts tablespace
		,DECODE(total.mb, NULL, 'OFFLINE', dbat.status) status
		,total.mb "Curr total MB"
		 ,total.MaxMb "Max Total MB"
		 ,NVL(total.mb - free.mb, total.mb) "Used MB"
		 ,NVL(free.mb, 0) "Curr Free_MB"
		 ,CASE
			 WHEN (total.mb IS NULL) THEN
			  '[' || RPAD(LPAD('OFFLINE', 13, '-'), 20, '-') || ']'
			 ELSE
			  '[' || DECODE(free.mb
						   ,NULL
						   ,'XXXXXXXXXXXXXXXXXXXX'
						   ,NVL(RPAD(LPAD('X', trunc((100 - ROUND((free.mb + (total.MaxMb - total.mb)) / (total.MaxMb) * 100, 2)) / 5), 'X'), 20, '-')
							   ,'--------------------')) || '] ' ||
			  to_char(DECODE(total.MaxMb, NULL, 0, NVL(ROUND((total.mb - free.mb) / (total.MaxMb) * 100, 2), 100))) || '%'
		 END AS "Graph %Max Size Used"
		 ,CASE
			 WHEN (total.mb IS NULL) THEN
			  '[' || RPAD(LPAD('OFFLINE', 13, '-'), 20, '-') || ']'
			 ELSE
			  '[' || DECODE(free.mb
						   ,NULL
						   ,'XXXXXXXXXXXXXXXXXXXX'
						   ,NVL(RPAD(LPAD('X', trunc((100 - ROUND((free.mb) / (total.mb) * 100, 2)) / 5), 'X'), 20, '-'), '--------------------')) || '] ' ||
			  to_char(DECODE(total.mb, NULL, 0, NVL(ROUND((total.mb - free.mb) / (total.mb) * 100, 2), 100))) || '%'
		 END AS "Graph %Current Size Used"
		 ,Total.AutoExtensible AutoExtensible
		 ,dbat.contents
		 ,dbat.extent_management
		 ,dbat.segment_space_management
		 ,dbat.logging

  FROM   (SELECT tablespace_name ts
				,MAX(autoextensible) AutoExtensible
				,SUM(bytes) / 1024 / 1024 mb
				,CASE
					 WHEN SUM(maxbytes) = 0 THEN
					  SUM(bytes) / 1024 / 1024
					 ELSE
					  SUM(maxbytes) / 1024 / 1024
				 END MaxMb
		  FROM   dba_data_files
		  GROUP  BY tablespace_name) total
		,(SELECT tablespace_name ts
				,SUM(bytes) / 1024 / 1024 mb
		  FROM   dba_free_space
		  GROUP  BY tablespace_name) free
		,dba_tablespaces dbat
  WHERE  total.ts = free.ts(+)
  AND    total.ts = dbat.tablespace_name
  UNION ALL
  SELECT sh.tablespace_name ts
		,'TEMP'
		,SUM(sh.bytes_used + sh.bytes_free) / 1024 / 1024
		,NULL
		,SUM(sh.bytes_used) / 1024 / 1024
		,SUM(sh.bytes_free) / 1024 / 1024
		,NULL
		,'[' || DECODE(SUM(sh.bytes_free)
					  ,0
					  ,'XXXXXXXXXXXXXXXXXXXX'
					  ,NVL(RPAD(LPAD('X', (TRUNC(ROUND((SUM(sh.bytes_used) / SUM(sh.bytes_used + sh.bytes_free)) * 100, 2) / 5)), 'X'), 20, '-')
						  ,'--------------------')) || ']'
		,NULL
		,'TEMPORARY'
		,NULL
		,NULL
		,NULL
  FROM   v$temp_space_header sh
  GROUP  BY tablespace_name
  ORDER  BY 2 ASC
		   ,7 DESC
		   ,5 ASC)
SELECT mst.*
	  ,CURSOR (SELECT *
			  FROM   det
			  WHERE  det.ts = mst.tablespace) Detail
FROM   mst
;