-- Utility Version - 1.0

ACCEPT my_sql_id CHAR PROMPT 'Enter SQL ID : '

VAR my_sql_id VARCHAR2(100)

EXEC :my_sql_id := '&&my_sql_id';

SET LINESIZE 32000 TRIMSPOOL ON VERIFY OFF PAGES 0 SERVEROUTPUT ON SIZE UNLIMITED ECHO OFF FEEDBACK OFF TIMING OFF

spool '&&my_sql_id'

EXEC DBMS_OUTPUT.PUT_LINE('BEGNING OF REPORT');

EXEC DBMS_OUTPUT.PUT_LINE('SQL_ID : '||:my_sql_id);

-- *** NAME_VALUE_PAIR START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : NAME_VALUE_PAIR');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;
	
	FOR pq_rec IN 	(
	SELECT
		        'DB_BLOCK_SIZE'
		||'^^'|| value
		||'^^'|| 'EOL'
		AS pq_record
	FROM v$parameter
	WHERE name = 'db_block_size')
	LOOP
	
		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);	
	
	END LOOP;
	
	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;	
	
END;
/

-- *** NAME_VALUE_PAIR END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
	
-- *** DBACE_SQL_PROFILES START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : DBACE_SQL_PROFILES');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;
	
	FOR pq_rec IN 	(
	SELECT 
		         name          
		||'^^'|| category      
		||'^^'|| signature     
		||'^^'|| sql_text      
		||'^^'|| TO_CHAR(created, 'MM/DD/YYYY HH24:MI:SS')       
		||'^^'|| TO_CHAR(last_modified, 'MM/DD/YYYY HH24:MI:SS') 
		||'^^'|| description   
		||'^^'|| type          
		||'^^'|| status        
		||'^^'|| force_matching
		||'^^'|| task_id       
		||'^^'|| task_exec_name
		||'^^'|| task_obj_id   
		||'^^'|| task_fnd_id   
		||'^^'|| task_rec_id   
		||'^^'|| task_con_dbid
		||'^^'|| 'EOL'
		AS pq_record		
	FROM dba_sql_profiles
	WHERE name = (SELECT DISTINCT sql_profile FROM gv$sqlarea
					WHERE sql_id = l_sql_id))
	LOOP
	
		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);	
	
	END LOOP;
	
	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
	
END;
/
	
-- *** DBACE_SQL_PROFILES END ***
	
SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
	
-- *** PQ$SESSION START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ$SESSION');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id; 
 
	FOR pq_rec IN 	(	
		SELECT	inst_id                                            
			||'^^'|| CAST(saddr AS VARCHAR2(2000))
			||'^^'|| sid                                                
			||'^^'|| serial#                                            
			||'^^'|| audsid                                             
			||'^^'|| CAST(paddr AS VARCHAR2(2000))            
			||'^^'|| user#                                              
			||'^^'|| username                                           
			||'^^'|| command                                            
			||'^^'|| ownerid                                            
			||'^^'|| taddr                                              
			||'^^'|| lockwait                                           
			||'^^'|| status                                             
			||'^^'|| server                                             
			||'^^'|| schema#                                            
			||'^^'|| schemaname                                         
			||'^^'|| osuser                                             
			||'^^'|| process                                            
			||'^^'|| machine                                            
			||'^^'|| port                                               
			||'^^'|| terminal                                           
			||'^^'|| program                                            
			||'^^'|| type                                               
			||'^^'|| CAST(sql_address AS VARCHAR2(2000))
			||'^^'|| sql_hash_value                                     
			||'^^'|| sql_id                                             
			||'^^'|| sql_child_number                                   
			||'^^'|| TO_CHAR(sql_exec_start, 'YYYY-MM-DD HH24:MI:SS')                                     
			||'^^'|| sql_exec_id                                        
			||'^^'|| CAST(prev_sql_addr AS VARCHAR2(2000))
			||'^^'|| prev_hash_value                                    
			||'^^'|| prev_sql_id                                        
			||'^^'|| prev_child_number                                  
			||'^^'|| TO_CHAR(prev_exec_start, 'YYYY-MM-DD HH24:MI:SS')                                  
			||'^^'|| prev_exec_id                                       
			||'^^'|| plsql_entry_object_id                              
			||'^^'|| plsql_entry_subprogram_id                          
			||'^^'|| plsql_object_id                                    
			||'^^'|| plsql_subprogram_id                                
			||'^^'|| module                                             
			||'^^'|| module_hash                                        
			||'^^'|| action                                             
			||'^^'|| action_hash                                        
			||'^^'|| client_info                                        
			||'^^'|| fixed_table_sequence                               
			||'^^'|| row_wait_obj#                                      
			||'^^'|| row_wait_file#                                     
			||'^^'|| row_wait_block#                                    
			||'^^'|| row_wait_row#                                      
			||'^^'|| top_level_call#                                    
			||'^^'|| TO_CHAR(logon_time, 'YYYY-MM-DD HH24:MI:SS')
			||'^^'|| last_call_et                                       
			||'^^'|| pdml_enabled                                       
			||'^^'|| failover_type                                      
			||'^^'|| failover_method                                    
			||'^^'|| failed_over                                        
			||'^^'|| resource_consumer_group                            
			||'^^'|| pdml_status                                        
			||'^^'|| pddl_status                                        
			||'^^'|| pq_status                                          
			||'^^'|| current_queue_duration                             
			||'^^'|| client_identifier                                  
			||'^^'|| blocking_session_status                            
			||'^^'|| blocking_instance                                  
			||'^^'|| blocking_session                                   
			||'^^'|| final_blocking_session_status                      
			||'^^'|| final_blocking_instance                            
			||'^^'|| final_blocking_session                             
			||'^^'|| seq#                                               
			||'^^'|| event#                                             
			||'^^'|| event                                              
			||'^^'|| p1text                                             
			||'^^'|| p1                                                 
			||'^^'|| CAST(p1raw AS VARCHAR2(2000))        
			||'^^'|| p2text                                             
			||'^^'|| p2                                                 
			||'^^'|| CAST(p2raw AS VARCHAR2(2000))      
			||'^^'|| p3text                                             
			||'^^'|| p3                                                 
			||'^^'|| CAST(p3raw AS VARCHAR2(2000))
			||'^^'|| wait_class_id                                      
			||'^^'|| wait_class#                                        
			||'^^'|| wait_class                                         
			||'^^'|| wait_time                                          
			||'^^'|| seconds_in_wait                                    
			||'^^'|| state                                              
			||'^^'|| wait_time_micro                                    
			||'^^'|| time_remaining_micro                               
			||'^^'|| time_since_last_wait_micro                         
			||'^^'|| service_name                                       
			||'^^'|| sql_trace                                          
			||'^^'|| sql_trace_waits                                    
			||'^^'|| sql_trace_binds                                    
			||'^^'|| sql_trace_plan_stats                               
			||'^^'|| session_edition_id                                 
			||'^^'|| CAST(creator_addr AS VARCHAR2(2000))
			||'^^'|| creator_serial#                                    
			||'^^'|| ecid                                               
			||'^^'|| sql_translation_profile_id                         
			||'^^'|| pga_tunable_mem                                    
			||'^^'|| shard_ddl_status                                   
			||'^^'|| con_id                                             
			||'^^'|| external_name                                      
			||'^^'|| plsql_debugger_connected
			||'^^'|| 'EOL'     
			AS pq_record                      
	FROM gv$session
	WHERE sql_id = l_sql_id)
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;
	
	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
	
END;
/
	
-- *** PQ$SESSION END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
    
-- *** PQ$SQLAREA START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ$SQLAREA');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	 
  
	FOR pq_rec IN (
		SELECT 	inst_id
			||'^^'|| sql_text
			||'^^'|| ''
			||'^^'|| sql_id
			||'^^'|| sharable_mem
			||'^^'|| persistent_mem
			||'^^'|| runtime_mem
			||'^^'|| sorts
			||'^^'|| version_count
			||'^^'|| loaded_versions
			||'^^'|| open_versions
			||'^^'|| users_opening
			||'^^'|| fetches
			||'^^'|| executions
			||'^^'|| px_servers_executions
			||'^^'|| end_of_fetch_count
			||'^^'|| users_executing
			||'^^'|| loads
			||'^^'|| first_load_time
			||'^^'|| invalidations
			||'^^'|| parse_calls
			||'^^'|| disk_reads
			||'^^'|| direct_writes
			||'^^'|| direct_reads
			||'^^'|| buffer_gets
			||'^^'|| application_wait_time
			||'^^'|| concurrency_wait_time
			||'^^'|| cluster_wait_time
			||'^^'|| user_io_wait_time
			||'^^'|| plsql_exec_time
			||'^^'|| java_exec_time
			||'^^'|| rows_processed
			||'^^'|| command_type
			||'^^'|| optimizer_mode
			||'^^'|| optimizer_cost
			||'^^'|| '' --CAST(optimizer_env AS VARCHAR2(2000))
			||'^^'|| optimizer_env_hash_value
			||'^^'|| parsing_user_id
			||'^^'|| parsing_schema_id
			||'^^'|| parsing_schema_name
			||'^^'|| kept_versions
			||'^^'|| '' --CAST(address AS VARCHAR2(2000))
			||'^^'|| hash_value
			||'^^'|| old_hash_value
			||'^^'|| plan_hash_value
			||'^^'|| full_plan_hash_value
			||'^^'|| module
			||'^^'|| module_hash
			||'^^'|| action
			||'^^'|| action_hash
			||'^^'|| serializable_aborts
			||'^^'|| outline_category
			||'^^'|| cpu_time
			||'^^'|| elapsed_time
			||'^^'|| outline_sid
			||'^^'|| '' --CAST(last_active_child_address AS VARCHAR2(2000))
			||'^^'|| remote
			||'^^'|| object_status
			||'^^'|| literal_hash_value
			||'^^'|| TO_CHAR(last_load_time, 'YYYY-MM-DD HH24:MI:SS')
			||'^^'|| is_obsolete
			||'^^'|| is_bind_sensitive
			||'^^'|| is_bind_aware
			||'^^'|| child_latch
			||'^^'|| sql_profile
			||'^^'|| sql_patch
			||'^^'|| sql_plan_baseline
			||'^^'|| program_id
			||'^^'|| program_line#
			||'^^'|| exact_matching_signature
			||'^^'|| force_matching_signature
			||'^^'|| TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS')
			||'^^'|| '' --CAST(bind_data AS VARCHAR2(2000))
			||'^^'|| typecheck_mem
			||'^^'|| io_cell_offload_eligible_bytes
			||'^^'|| io_interconnect_bytes
			||'^^'|| physical_read_requests
			||'^^'|| physical_read_bytes
			||'^^'|| physical_write_requests
			||'^^'|| physical_write_bytes
			||'^^'|| optimized_phy_read_requests
			||'^^'|| locked_total
			||'^^'|| pinned_total
			||'^^'|| io_cell_uncompressed_bytes
			||'^^'|| io_cell_offload_returned_bytes
			||'^^'|| con_id
			||'^^'|| is_reoptimizable
			||'^^'|| is_resolved_adaptive_plan
			||'^^'|| 'EOL'
			AS pq_record
	FROM 	gv$sqlarea 
	WHERE sql_id = l_sql_id)
	LOOP

		l_empty_flag := 1;
	
		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);
		
	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
	
-- *** PQ$SQLAREA END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- *** SFTEXT START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : SFTEXT');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	 

	FOR pq_rec IN (
		SELECT 	inst_id
			||'^^'|| sql_id
			||'^^'|| TO_CLOB((CASE WHEN dbms_lob.getlength(sql_fulltext ) <= 4000 THEN
						dbms_lob.substr(sql_fulltext ,LEAST(4000, dbms_lob.getlength(sql_fulltext )),1) 
					WHEN dbms_lob.getlength(sql_fulltext ) BETWEEN 4001 AND 8000 THEN
						dbms_lob.substr(sql_fulltext ,LEAST(4000, dbms_lob.getlength(sql_fulltext )),1) ||
						dbms_lob.substr(sql_fulltext ,LEAST(8000, dbms_lob.getlength(sql_fulltext )),4001) 
					WHEN dbms_lob.getlength(sql_fulltext ) BETWEEN 8001 AND 12000 THEN
						dbms_lob.substr(sql_fulltext ,LEAST(4000, dbms_lob.getlength(sql_fulltext )),1) ||
						dbms_lob.substr(sql_fulltext ,LEAST(8000, dbms_lob.getlength(sql_fulltext )),4001) || 			
						dbms_lob.substr(sql_fulltext ,LEAST(12000, dbms_lob.getlength(sql_fulltext )),8001)
					ELSE sql_text
				 END ))
			||'^^'|| 'EOL'
			AS pq_record		
	FROM 	gv$sqlarea 
	WHERE sql_id = l_sql_id)
	LOOP

		l_empty_flag := 1;

		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);
	
	END LOOP;	
END;
/

-- *** SFTEXT END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
    
-- *** PQ$SQL_PLAN_STATISTICS_ALL START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ$SQL_PLAN_STATISTICS_ALL');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	 
 
	FOR pq_rec IN (	
		SELECT	inst_id
			||'^^'|| CAST(address AS VARCHAR2(2000))
			||'^^'|| hash_value          
			||'^^'|| sql_id              
			||'^^'|| plan_hash_value     
			||'^^'|| full_plan_hash_value
			||'^^'|| CAST(child_address AS VARCHAR2(2000))
			||'^^'|| child_number
			||'^^'|| TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS')
			||'^^'|| operation                      
			||'^^'|| options                        
			||'^^'|| object_node                    
			||'^^'|| object#                       
			||'^^'|| object_owner                   
			||'^^'|| object_name                    
			||'^^'|| object_alias                   
			||'^^'|| object_type                    
			||'^^'|| optimizer                      
			||'^^'|| id                             
			||'^^'|| parent_id                      
			||'^^'|| depth                          
			||'^^'|| position                       
			||'^^'|| search_columns                 
			||'^^'|| cost                           
			||'^^'|| cardinality                    
			||'^^'|| bytes                          
			||'^^'|| other_tag                      
			||'^^'|| partition_start                
			||'^^'|| partition_stop                 
			||'^^'|| partition_id                   
			||'^^'|| '' --other                          
			||'^^'|| distribution                   
			||'^^'|| cpu_cost                       
			||'^^'|| io_cost                        
			||'^^'|| temp_space                     
			||'^^'|| access_predicates              
			||'^^'|| filter_predicates              
			||'^^'|| '' --projection                     
			||'^^'|| time                           
			||'^^'|| qblock_name                    
			||'^^'|| '' --remarks                                          
			||'^^'|| '' 
			||'^^'|| executions                     
			||'^^'|| last_starts                    
			||'^^'|| starts                         
			||'^^'|| last_output_rows               
			||'^^'|| output_rows                    
			||'^^'|| last_cr_buffer_gets            
			||'^^'|| cr_buffer_gets                 
			||'^^'|| last_cu_buffer_gets            
			||'^^'|| cu_buffer_gets                 
			||'^^'|| last_disk_reads                
			||'^^'|| disk_reads                     
			||'^^'|| last_disk_writes               
			||'^^'|| disk_writes                    
			||'^^'|| last_elapsed_time              
			||'^^'|| elapsed_time                   
			||'^^'|| policy                         
			||'^^'|| estimated_optimal_size         
			||'^^'|| estimated_onepass_size         
			||'^^'|| last_memory_used               
			||'^^'|| last_execution                 
			||'^^'|| last_degree                    
			||'^^'|| total_executions               
			||'^^'|| optimal_executions             
			||'^^'|| onepass_executions             
			||'^^'|| multipasses_executions         
			||'^^'|| active_time                    
			||'^^'|| max_tempseg_size               
			||'^^'|| last_tempseg_size              
			||'^^'|| con_id                         
			||'^^'|| con_dbid
			||'^^'|| 'EOL'  
			AS pq_record
	FROM	gv$sql_plan_statistics_all
	WHERE sql_id = l_sql_id)
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/

-- *** GV$SQL_PLAN_STATISTICS_ALL END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
    
-- *** PQ$SQL_PLAN_MONITOR START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ$SQL_PLAN_MONITOR'); 

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	
 
	FOR pq_rec IN (	
		SELECT	inst_id                                           
			||'^^'|| con_id                                           
			||'^^'|| key                                              
			||'^^'|| status                                           
			||'^^'|| TO_CHAR(first_refresh_time, 'YYYY-MM-DD HH24:MI:SS')
			||'^^'|| TO_CHAR(last_refresh_time, 'YYYY-MM-DD HH24:MI:SS')                               
			||'^^'|| TO_CHAR(first_change_time, 'YYYY-MM-DD HH24:MI:SS')                              
			||'^^'|| TO_CHAR(last_change_time, 'YYYY-MM-DD HH24:MI:SS')                               
			||'^^'|| refresh_count                                    
			||'^^'|| sid                                              
			||'^^'|| process_name                                     
			||'^^'|| sql_id                                           
			||'^^'|| TO_CHAR(sql_exec_start, 'YYYY-MM-DD HH24:MI:SS')                                  
			||'^^'|| sql_exec_id                                      
			||'^^'|| sql_plan_hash_value                              
			||'^^'|| '' --CAST(sql_child_address AS VARCHAR2(2000))
			||'^^'|| plan_parent_id                         
			||'^^'|| plan_line_id             
			||'^^'|| plan_operation           
			||'^^'|| plan_options             
			||'^^'|| plan_object_owner        
			||'^^'|| plan_object_name         
			||'^^'|| plan_object_type         
			||'^^'|| plan_depth               
			||'^^'|| plan_position            
			||'^^'|| plan_cost                
			||'^^'|| plan_cardinality         
			||'^^'|| plan_bytes               
			||'^^'|| plan_time                
			||'^^'|| plan_partition_start     
			||'^^'|| plan_partition_stop      
			||'^^'|| plan_cpu_cost            
			||'^^'|| plan_io_cost             
			||'^^'|| plan_temp_space                        
			||'^^'|| starts                                 
			||'^^'|| output_rows                            
			||'^^'|| io_interconnect_bytes                  
			||'^^'|| physical_read_requests                 
			||'^^'|| physical_read_bytes                    
			||'^^'|| physical_write_requests                
			||'^^'|| physical_write_bytes                   
			||'^^'|| workarea_mem                           
			||'^^'|| workarea_max_mem                       
			||'^^'|| workarea_tempseg                       
			||'^^'|| workarea_max_tempseg                   
			||'^^'|| otherstat_group_id                     
			||'^^'|| '' --otherstat_1_id                         
			||'^^'|| '' --otherstat_1_type                       
			||'^^'|| '' --otherstat_1_value                      
			||'^^'|| '' --otherstat_2_id                         
			||'^^'|| '' --otherstat_2_type                       
			||'^^'|| '' --otherstat_2_value                      
			||'^^'|| '' --otherstat_3_id                         
			||'^^'|| '' --otherstat_3_type                       
			||'^^'|| '' --otherstat_3_value                      
			||'^^'|| '' --otherstat_4_id                         
			||'^^'|| '' --otherstat_4_type                       
			||'^^'|| '' --otherstat_4_value                      
			||'^^'|| '' --otherstat_5_id                         
			||'^^'|| '' --otherstat_5_type                       
			||'^^'|| '' --otherstat_5_value                      
			||'^^'|| '' --otherstat_6_id                         
			||'^^'|| '' --otherstat_6_type                       
			||'^^'|| '' --otherstat_6_value                      
			||'^^'|| '' --otherstat_7_id                         
			||'^^'|| '' --otherstat_7_type                       
			||'^^'|| '' --otherstat_7_value                      
			||'^^'|| '' --otherstat_8_id                         
			||'^^'|| '' --otherstat_8_type                       
			||'^^'|| '' --otherstat_8_value                      
			||'^^'|| '' --otherstat_9_id                         
			||'^^'|| '' --otherstat_9_type                       
			||'^^'|| '' --otherstat_9_value                      
			||'^^'|| '' --otherstat_10_id                        
			||'^^'|| '' --otherstat_10_type                      
			||'^^'|| '' --otherstat_10_value                                               
			||'^^'|| ''
			||'^^'|| plan_operation_inactive   
			||'^^'|| 'EOL'
			AS pq_record             
	FROM gv$sql_plan_monitor
	WHERE sql_id = l_sql_id)
	LOOP
		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/

-- *** GV$SQL_PLAN_MONITOR END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- *** PQ$TABLE_STATS START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ_TABLE_STATS'); 

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	
 
	FOR pq_rec IN (
		SELECT    l_sql_id
				||'^^'|| owner
				||'^^'|| table_name
				||'^^'|| tablespace_name
				||'^^'|| temporary
				||'^^'|| ROUND (num_rows)
				||'^^'|| ROUND (DECODE (num_rows, 0, 0, sample_size * 100 / num_rows), 2)
				||'^^'|| TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS')
				||'^^'|| ROUND (avg_row_len * num_rows / 8192)
				||'^^'|| blocks
				||'^^'|| DECODE (num_rows,
						0, 0,
						((blocks - ROUND (avg_row_len * num_rows / 8192)) * 100
						)
						/ blocks
						) 
				||'^^'|| LTRIM(RTRIM(degree))
				||'^^'||( 	SELECT MAX(stale_stats) FROM dba_tab_statistics d 
							WHERE d.table_name = a.table_name 
							AND d.owner =  a.owner 
							AND d.object_type = 'TABLE')
				||'^^'|| 'EOL'
			AS pq_record
	FROM dba_tables a
	WHERE (owner, table_name) IN (
				SELECT object_owner, object_name
				FROM gv$sql_plan_statistics_all a
				WHERE sql_id = l_sql_id
				AND EXISTS (
						SELECT NULL
							FROM dba_tables b
						WHERE a.object_owner = b.owner
							AND a.object_name = b.table_name)
				UNION
				SELECT b.table_owner, b.table_name
				FROM gv$sql_plan_statistics_all a, dba_indexes b
				WHERE sql_id = l_sql_id
				AND a.object_owner = b.owner
				AND a.object_name = index_name)
	-- AND num_rows <> 0
	ORDER BY num_rows DESC)
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
       
-- *** PQ_TABLE_STATS END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- *** PQ$INDEX_STATS START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ_INDEX_STATS');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	 
 
	FOR pq_rec IN (
		SELECT    		 l_sql_id
				||'^^'|| i.owner
				||'^^'|| i.table_owner
				||'^^'|| i.table_name
				||'^^'|| i.index_name
				||'^^'|| c.column_name
				||'^^'|| c.column_position
				||'^^'|| i.tablespace_name
				||'^^'|| i.status
				||'^^'|| i.blevel
				||'^^'|| i.leaf_blocks
				||'^^'|| ROUND (i.num_rows)
				||'^^'|| DECODE (i.num_rows,
								0, 0,
								ROUND (i.sample_size * 100 / i.num_rows, 2)
							   )
				||'^^'|| TO_CHAR(i.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
				||'^^'|| i.clustering_factor
				||'^^'|| i.uniqueness
				||'^^'|| i.degree
				||'^^'|| 'EOL'
			AS pq_record
	FROM dba_indexes i, 
	  	 dba_ind_columns c
	WHERE (i.table_owner, i.table_name) IN (
				SELECT 	  object_owner
						, (CASE WHEN (SELECT object_type FROM dba_objects WHERE owner = a.object_owner AND object_name = a.object_name AND ROWNUM <= 1) = 'INDEX' 
								THEN (SELECT table_name FROM dba_indexes WHERE table_owner = a.object_owner AND index_name = a.object_name AND ROWNUM <= 1)
								ELSE object_name END) AS table_name
				FROM gv$sql_plan_statistics_all a
				WHERE sql_id = l_sql_id
				)
	AND i.index_name = c.index_name
	AND i.table_owner = c.table_owner
	AND i.table_name = c.table_name
	--ORDER BY 1, 2, 4 DESC
	)
	LOOP
		l_empty_flag := 1;

		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
								
-- *** PQ_INDEX_STATS END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- *** PQ$ALL_INDEX_STATS START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ_ALL_INDEX_STATS'); 

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	
 
	FOR pq_rec IN (
		SELECT  /*+ PARALLEL(8) */
					 l_sql_id
			||'^^'|| i.owner
			||'^^'|| i.table_owner
			||'^^'|| i.table_name
			||'^^'|| i.index_name
			||'^^'|| c.column_name
			||'^^'|| c.column_position
            ||'^^'|| i.tablespace_name
            ||'^^'|| i.status
			||'^^'|| i.blevel
			||'^^'|| i.leaf_blocks
			||'^^'|| ROUND (i.num_rows)
			||'^^'|| DECODE (i.num_rows,
							0, 0,
							ROUND (i.sample_size * 100 / i.num_rows, 2)
							)
			||'^^'|| TO_CHAR(i.last_analyzed,'YYYY-MM-DD HH24:MI:SS')
			||'^^'|| i.clustering_factor
			||'^^'|| i.uniqueness
			||'^^'|| i.degree
			||'^^'|| 'EOL'
			AS pq_record
	FROM   	  dba_indexes i
			, dba_ind_columns c
	WHERE   i.index_name = c.index_name
	AND i.owner = c.table_owner
	AND i.table_name = c.table_name
	AND (c.table_owner, c.table_name, c.column_name) IN (SELECT    owner
									 , table_name
									 , column_name
							FROM 	 dba_tab_cols
							WHERE (owner, table_name) IN (																		SELECT object_owner, object_name
								FROM gv$sql_plan_statistics_all
								WHERE object_type LIKE 'TABLE%'
								AND   sql_id = l_sql_id
								UNION
								SELECT b.table_owner, b.table_name
								FROM gv$sql_plan_statistics_all a, dba_indexes b
								WHERE a.sql_id = l_sql_id
								AND a.object_owner = b.owner
								AND a.object_name = b.index_name)
								AND EXISTS ( SELECT 1  FROM gv$sql_plan_statistics_all a
									     WHERE a.sql_id = l_sql_id
									   AND INSTR (a.access_predicates || '   ' || a.filter_predicates,												  column_name																			 ) > 0)	)
	--ORDER BY 1, 2, 3, 5
	)
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
								
-- *** PQ_ALL_INDEX_STATS END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- *** PQ$COLUMN_STATS START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : PQ_COLUMN_STATS'); 

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	
 
	FOR pq_rec IN (
		SELECT  	l_sql_id
			||'^^'|| owner
			||'^^'|| table_name
			||'^^'|| column_name
			||'^^'|| DECODE(data_type, 'VARCHAR2', data_type||'('||data_length||')', data_type) 
			||'^^'|| nullable
			||'^^'|| num_nulls
			||'^^'|| num_distinct
			||'^^'|| density
			||'^^'|| histogram
			||'^^'|| 'EOL'
			AS pq_record
	FROM dba_tab_cols
	WHERE (owner, table_name) IN (
				SELECT object_owner, object_name
				FROM gv$sql_plan_statistics_all a
				WHERE sql_id = l_sql_id
				AND EXISTS (
						SELECT NULL
							FROM dba_tables b
						WHERE a.object_owner = b.owner
							AND a.object_name = table_name)
				UNION
				SELECT b.table_owner, b.table_name
				FROM gv$sql_plan_statistics_all a, dba_indexes b
				WHERE a.sql_id = l_sql_id
				AND a.object_owner = b.owner
				AND a.object_name = index_name)
		AND EXISTS (
				SELECT 1
				FROM gv$sql_plan_statistics_all a
				WHERE a.sql_id = l_sql_id
				AND INSTR (access_predicates || '   ' || filter_predicates,
							column_name
							) > 0)
	--ORDER BY 1, 2
	)
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
								
-- *** PQ_COLUMN_STATS END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
 
-- *** DBACE_TABLESPACES START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : DBACE_TABLESPACES');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	 

	FOR pq_rec IN (
		SELECT			 l_sql_id
				||'^^'|| tablespace_name          
				||'^^'|| block_size               
				||'^^'|| initial_extent           
				||'^^'|| next_extent              
				||'^^'|| ROUND(min_extents, 0)
				||'^^'|| ROUND(max_extents, 0)             
				||'^^'|| ROUND(max_size, 0)            
				||'^^'|| ROUND(pct_increase, 0)       
				||'^^'|| ROUND(min_extlen, 0)           
				||'^^'|| status                   
				||'^^'|| contents                 
				||'^^'|| logging                  
				||'^^'|| force_logging            
				||'^^'|| extent_management        
				||'^^'|| allocation_type          
				||'^^'|| plugged_in               
				||'^^'|| segment_space_management 
				||'^^'|| def_tab_compression      
				||'^^'|| retention                
				||'^^'|| bigfile                  
				||'^^'|| predicate_evaluation     
				||'^^'|| encrypted                
				||'^^'|| compress_for             
				||'^^'|| def_inmemory             
				||'^^'|| def_inmemory_priority    
				||'^^'|| def_inmemory_distribute  
				||'^^'|| def_inmemory_compression 
				||'^^'|| def_inmemory_duplicate   
				||'^^'|| shared                   
				||'^^'|| def_index_compression    
				||'^^'|| index_compress_for       
				||'^^'|| def_cellmemory           
				||'^^'|| def_inmemory_service     
				||'^^'|| def_inmemory_service_name
				||'^^'|| lost_write_protect       
				||'^^'|| chunk_tablespace  
				||'^^'|| 'EOL'  
				AS pq_record				
	FROM	dba_tablespaces
	WHERE tablespace_name IN (	SELECT t.tablespace_name
								FROM gv$sql_plan_statistics_all a, dba_tables t
								WHERE sql_id = l_sql_id
								AND  a.object_owner = t.owner
								AND  a.object_name  = t.table_name
								UNION
								SELECT i.tablespace_name
								FROM gv$sql_plan_statistics_all a, dba_indexes i
								WHERE sql_id = l_sql_id
								AND a.object_owner = i.owner
								AND a.object_name = i.index_name
								UNION
								SELECT tablespace_name
								FROM dba_tablespaces
								WHERE contents = 'UNDO'))
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
	
-- *** DBACE_TABLESPACES END ***	

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED

-- *** DBACE_DATA_FILES START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : DBACE_DATA_FILES'); 

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	
 
	FOR pq_rec IN (
		SELECT  	     l_sql_id
				||'^^'|| file_name         
				||'^^'|| file_id           
				||'^^'|| tablespace_name   
				||'^^'|| bytes             
				||'^^'|| blocks            
				||'^^'|| status            
				||'^^'|| relative_fno      
				||'^^'|| autoextensible    
				||'^^'|| maxbytes          
				||'^^'|| maxblocks         
				||'^^'|| increment_by      
				||'^^'|| user_bytes        
				||'^^'|| user_blocks       
				||'^^'|| online_status     
				||'^^'|| lost_write_protect
				||'^^'|| 'EOL'
				AS pq_record
	FROM dba_data_files
	WHERE tablespace_name IN (	SELECT t.tablespace_name
								FROM gv$sql_plan_statistics_all a, dba_tables t
								WHERE sql_id = l_sql_id
								AND  a.object_owner = t.owner
								AND  a.object_name  = t.table_name
								UNION
								SELECT i.tablespace_name
								FROM gv$sql_plan_statistics_all a, dba_indexes i
								WHERE sql_id = l_sql_id
								AND a.object_owner = i.owner
								AND a.object_name = i.index_name
								UNION
								SELECT tablespace_name
								FROM dba_tablespaces
								WHERE contents = 'UNDO'))
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/
 
-- *** DBACE_DATA_FILES END ***

SET SERVEROUTPUT OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
	
-- *** DBACE_VIEWS START ***

EXEC DBMS_OUTPUT.PUT_LINE('TABLE : DBACE_VIEWS');

DECLARE

	l_sql_id VARCHAR2(100);
	l_empty_flag NUMBER(1) := 0;
	
BEGIN

	l_sql_id := :my_sql_id;	 	

	FOR pq_rec IN (
	SELECT 			
			owner||'.'||view_name
			||'^^'||TO_CLOB(RTRIM(text_vc))
			||'^^'|| 'EOL'
			AS pq_record
	FROM dba_views
	WHERE (owner, view_name) IN (	SELECT object_owner, object_name 
									FROM gv$sql_plan_statistics_all
									WHERE sql_id = l_sql_id
									AND   object_type = 'VIEW'
									AND   object_name NOT LIKE '%$%'))
	LOOP

		l_empty_flag := 1;
 
 		DBMS_OUTPUT.PUT_LINE(pq_rec.pq_record);

	END LOOP;

	IF l_empty_flag = 0 THEN
		
		DBMS_OUTPUT.PUT_LINE('NO DATA');

	END IF;
		
END;
/	
		
EXEC DBMS_OUTPUT.PUT_LINE('END OF REPORT');

SPOOL OFF
