Re: Oracle Sqlplus Report

From: Sanjay Mishra <"Sanjay>
Date: Wed, 3 May 2017 14:12:53 +0000 (UTC)
Message-ID: <1887992741.1942656.1493820773722_at_mail.yahoo.com>

Binh
Thanks for your time and yes it works as I am looking. I will manipulate this to adjust the timelines. Thanks again for your time RgdsSanjay

    On Saturday, April 29, 2017 9:21 PM, "Le, Binh T." <Binh.Le_at_lfg.com> wrote:  

 #yiv1779377981 #yiv1779377981 -- _filtered #yiv1779377981 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv1779377981 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv1779377981 #yiv1779377981 p.yiv1779377981MsoNormal, #yiv1779377981 li.yiv1779377981MsoNormal, #yiv1779377981 div.yiv1779377981MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv1779377981 a:link, #yiv1779377981 span.yiv1779377981MsoHyperlink {color:#0563C1;text-decoration:underline;}#yiv1779377981 a:visited, #yiv1779377981 span.yiv1779377981MsoHyperlinkFollowed {color:#954F72;text-decoration:underline;}#yiv1779377981 p.yiv1779377981msonormal0, #yiv1779377981 li.yiv1779377981msonormal0, #yiv1779377981 div.yiv1779377981msonormal0 {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv1779377981 span.yiv1779377981EmailStyle18 {color:windowtext;}#yiv1779377981 .yiv1779377981MsoChpDefault {font-size:10.0pt;} _filtered #yiv1779377981 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv1779377981 div.yiv1779377981WordSection1 {}#yiv1779377981 Try this ….     with table_t as (SELECT ob.owner owner, ob.object_name object_name, rowcnt,to_char(analyzetime,'yyyymmdd') 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) select table_t.owner,        table_t.object_name,        max(case when table_t.analyzetime = '20170301' then table_t.rowncnt else null end) as "20170301",        max(case when table_t.analyzetime = '20170302' then table_t.rowncnt else null end) as "20170302",        max(case when table_t.analyzetime = '20170303' then table_t.rowncnt else null end) as "20170303",        max(case when table_t.analyzetime = '20170304' then table_t.rowncnt else null end) as "20170304",        max(case when table_t.analyzetime = '20170305' then table_t.rowncnt else null end) as "20170305"   from table_t group by table_t.owner,table_t.object_name;   From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sanjay Mishra Sent: Saturday, April 29, 2017 1:42 PM
To: Oracle-L Freelists <oracle-l_at_freelists.org> Subject: Oracle Sqlplus Report   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        Tx Sanjay Notice of Confidentiality: **This E-mail and any of its attachments may contain 

Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**    

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 03 2017 - 16:12:53 CEST