Oracle Sqlplus Report

  • From: "Sanjay Mishra" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "smishra_97" for DMARC)
  • To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 29 Apr 2017 17:41:56 +0000 (UTC)

I am running query to get the data in rows to be converted to column as shown 
below and so need help for SQL experts

SELECT ob.owner, ob.object_name, rowcnt,  analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE owner='INFODBA'
and object_name in (select table_name from dba_tables where owner='DEMOUSER' 
and num_rows > 100000)
and object_type in ('TABLE')
and object_id=obj#
order by savtime asc;

Original Format================OWNER      OBJECT_NAME              ROWCNT     
ANALYZETIME
---------- -------------------     ---------------    
---------------------------    
SAMPLE    DEMO1                     3233336      20170301     
SAMPLE    DEMO1                     3233990      20170302      SAMPLE    DEMO1  
                   3234543      20170303     SAMPLE    DEMO1                    
 3235324      20170304     SAMPLE    DEMO1                     3235487       
20170305     
SAMPLE    DEMO2                      706595       20170301     
SAMPLE    DEMO2                      706595       20170302    
SAMPLE    DEMO2                      706595       20170303    
SAMPLE    DEMO2                      706595       20170304    
SAMPLE    DEMO2                      706595       20170305    

SAMPLE    DEMO3                     9173498       20170301     
SAMPLE    DEMO3                     9173498       20170302    
SAMPLE    DEMO3                     9173498       20170303    
SAMPLE    DEMO3                     9173498       20170304    
SAMPLE    DEMO3                     9173498       20170305    


Required Format
OBJECT_NAME    20170301     20170302    20170303    20170304    20170305    
--------------    -------------- --------------     --------------     
--------------     --------------     
DEMO1       3233336        3233990        3234543        3235324        3235324 
       
DEMO2        706595        706595        706595        706595        706595     
   
DEMO3        9173498        9173498        9173498        9173498        
9173498 


TxSanjay

Other related posts: