tag:blogger.com,1999:blog-79329914576433309342024-03-12T20:46:53.604-07:00Technical solutions for excellence in businessBlog from a software professional, Passionate to work in latest cutting edge technologies in order to get better results in business.Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.comBlogger167125tag:blogger.com,1999:blog-7932991457643330934.post-53589609183618548462015-11-24T02:58:00.001-08:002015-11-24T05:28:20.210-08:00Oracle ORA-00020 : Maximum Number of Processes(XXX) Exceeded.<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="WordSection1">
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Hi,<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">When connecting to oracle sometimes, user may face this error: <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<i><span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Maximum Number of Processes(XXX) Exceeded.<o:p></o:p></span></i></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">The reason is the process in the server are in use.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Solution: <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: -.25in;">
<![if !supportLists]><span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;"><span style="mso-list: Ignore;">1.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><![endif]><span dir="LTR"></span><span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Is to increase the value of <b>Processes</b> in initialization parameter.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .25in;">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Or <o:p></o:p></span></div>
<div class="MsoNormal" style="text-indent: .25in;">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: -.25in;">
<![if !supportLists]><span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;"><span style="mso-list: Ignore;">2.<span style="font: 7.0pt "Times New Roman";"> </span></span></span><![endif]><span dir="LTR"></span><span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Kill the long time ideal session to free the processes. From oracle and in unix if needed <o:p></o:p></span></div>
<div class="MsoNormal" style="text-indent: .25in;">
<br /></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">--List of processes not connected to sessions (Oracle8i):<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">***********************************************<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #44546a; font-family: "verdana" , sans-serif; font-size: 10.0pt;">select 'kill -9 '|| SPID || ';' from v$process where program!= 'PSEUDO'and addr not in (select paddr from v$session)and addr not in (select paddr from v$bgprocess)and addr not in (select paddr from v$shared_server);<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f497d; font-family: "verdana" , sans-serif; font-size: 10.0pt;">Thanks & Regards<o:p></o:p></span></div>
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse;"> <tbody>
<tr> <td style="padding: 0in 0in 0in 0in; width: 56.25pt;" valign="top" width="75"><div class="MsoNormal">
<span style="color: #1f497d;"><br /> Fauzi</span><span style="color: #1f497d; font-size: 12.0pt;"><o:p></o:p></span></div>
</td> <td style="padding: 0in 0in 0in 0in;"></td> </tr>
</tbody> </table>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<br /></div>
</div>
</div>
Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-73230201044170440952015-11-20T20:49:00.005-08:002015-11-20T21:15:23.338-08:00The remote server returned an error: (407) Proxy Authentication Required.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: "verdana" , sans-serif;"><br /><br /><span style="font-family: verdana, sans-serif;">Hello,</span><br /><br /><span style="font-family: verdana, sans-serif;">I was writing an asp.net utility(console application) to fetch an image from a URL. During development i faced the following message: </span><br /><br /><i style="font-family: verdana, sans-serif;"><span style="color: blue;">The remote server returned an error: (407) Proxy Authentication Required.</span></i><br /><br /><span style="font-family: Verdana, sans-serif;">The solution is we need to place a app.config file to the project and add the following nodes</span></span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;"><br /></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOicdhbZ_EUHSj5piABPx-LpiNZAJMdP2W0jY-8Dr9Gckf2U7MruCB7SF23THQiI3VK8WCrpXdX9rvRG8_ZxS52l1ewaBwFcgnRoZrHqhHxUPIbmQMAvLqM-Xa7drbYkElfDvPcKLLGdqM/s1600/Untitled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Verdana, sans-serif;"><img border="0" height="68" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOicdhbZ_EUHSj5piABPx-LpiNZAJMdP2W0jY-8Dr9Gckf2U7MruCB7SF23THQiI3VK8WCrpXdX9rvRG8_ZxS52l1ewaBwFcgnRoZrHqhHxUPIbmQMAvLqM-Xa7drbYkElfDvPcKLLGdqM/s320/Untitled.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana, sans-serif;">defaultProxy useDefaultCredentials="true"</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana, sans-serif;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Verdana, sans-serif;">Hope it helps...</span></div>
</div>
</div>
Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-43570314248476369582014-03-13T01:56:00.001-07:002014-03-13T01:56:50.082-07:00Startup The Database In Archivelog Mode / NoArchivelog Mode<div class="WordSection1"> <table class="MsoNormalTable" border="1" cellspacing="1" cellpadding="0" width="100%" style="width:100.0%;background:#D8D8C4"> <tbody> <tr> <td width="100%" colspan="2" style="width:100.0%;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><b><span style="font-size:12.0pt;font-family:"Arial","sans-serif";color:olive">Startup The Database In Archivelog Mode</span></b><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><o:p></o:p></span></p> </td> </tr> <tr> <td width="25%" valign="top" style="width:25.0%;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""><br> Steps Required To Take A Database Not In Archive Log Mode And Alter It To Archive Log Mode</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><o:p></o:p></span></p> </td> <td width="75%" style="width:75.0%;background:#80FFFF;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><span style="font-size:12.0pt;font-family:Courier"><a href="http://psoug.org/definition/SELECT.htm"><span style="color:blue">SELECT</span></a> log_mode<br> <a href="http://psoug.org/definition/FROM.htm"><span style="color:blue">FROM</span></a> v$database;<br> <br> SHUTDOWN;<br> <br> STARTUP MOUNT EXCLUSIVE;<br> <br> <a href="http://psoug.org/definition/ALTER.htm"><span style="color:blue">ALTER</span></a> DATABASE <span style="color:blue">ARCHIVELOG</span>;<br> <br> <a href="http://psoug.org/definition/ALTER.htm"><span style="color:blue">ALTER</span></a> DATABASE OPEN;<br> <br> <a href="http://psoug.org/definition/SELECT.htm"><span style="color:blue">SELECT</span></a> log_mode<br> <a href="http://psoug.org/definition/FROM.htm"><span style="color:blue">FROM</span></a> v$database;</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""> <o:p></o:p></span></p> </td> </tr> <tr> <td width="100%" colspan="2" style="width:100.0%;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><span style="font-size:12.0pt;font-family:"Times New Roman","serif""> <o:p></o:p></span></p> </td> </tr> <tr> <td width="100%" colspan="2" style="width:100.0%;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><b><span style="font-size:12.0pt;font-family:"Arial","sans-serif";color:olive">Startup The Database In NoArchivelog Mode</span></b><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><o:p></o:p></span></p> </td> </tr> <tr> <td width="25%" valign="top" style="width:25.0%;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif""><br> Steps Required To Take A Database In Archive Log Mode And Alter It To No Archive Log Mode</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""> <o:p></o:p></span></p> </td> <td width="75%" style="width:75.0%;background:#80FFFF;padding:1.5pt 1.5pt 1.5pt 1.5pt"> <p class="MsoNormal"><span style="font-size:12.0pt;font-family:Courier"><a href="http://psoug.org/definition/SELECT.htm"><span style="color:blue">SELECT</span></a> log_mode<br> <a href="http://psoug.org/definition/FROM.htm"><span style="color:blue">FROM</span></a> v$database;<br> <br> SHUTDOWN;<br> <br> STARTUP MOUNT EXCLUSIVE;<br> <br> <a href="http://psoug.org/definition/ALTER.htm"><span style="color:blue">ALTER</span></a> DATABASE <span style="color:blue">NOARCHIVELOG</span>;<br> <br> <a href="http://psoug.org/definition/ALTER.htm"><span style="color:blue">ALTER</span></a> DATABASE OPEN;<br> <br> <a href="http://psoug.org/definition/SELECT.htm"><span style="color:blue">SELECT</span></a> log_mode<br> <a href="http://psoug.org/definition/FROM.htm"><span style="color:blue">FROM</span></a> v$database;</span><span style="font-size:12.0pt;font-family:"Times New Roman","serif""><o:p></o:p></span></p> </td> </tr> </tbody> </table> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Source: http://psoug.org/reference/archivelog.html<o:p></o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-20122453990744438852014-02-06T00:24:00.001-08:002014-02-06T00:24:18.268-08:00allowDefinition='MachineToApplication' beyond application level <div class="WordSection1"> <p class="MsoNormal"><span style="color:#1F497D">When am trying to build a web application through visual studio 2010, I was getting the following error message.<o:p></o:p></span></p> <p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:11.25pt;margin-left:0in;text-indent:-.25in;line-height:18.0pt;mso-list:l0 level1 lfo1"> <![if !supportLists]><span lang="EN" style="font-size:10.0pt;font-family:Symbol"><span style="mso-list:Ignore">·<span style="font:7.0pt "Times New Roman""> </span></span></span><![endif]><span dir="LTR"></span><b><span lang="EN" style="font-size:10.5pt;font-family:"Segoe UI","sans-serif"">"It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. <br> This error can be caused by a virtual directory not being configured as an application in IIS. "</span></b><span lang="EN" style="font-size:10.5pt;font-family:"Segoe UI","sans-serif""><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">The solution is to check whether the is any other sub folder which is having web.config file. After removing it, The build was successful.<o:p></o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="color:#1F497D">Best Regards<o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-45456339167277067312013-11-17T21:45:00.001-08:002013-11-17T21:45:24.902-08:00Grants given to objects through Roles for a Schema & without Roles (Direct)<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Hi Folks,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">The following queries are very much useful to get the list of Grants given to Roles for a particular schema objects & to List the direct grants given to users.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Grants given to objects through Roles for a Schema<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">**************************************************<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">SELECT TP.OWNER,TP.GRANTEE,RP.GRANTEE, TP.TABLE_NAME, TP.PRIVILEGE FROM DBA_TAB_PRIVS TP, DBA_ROLE_PRIVS RP WHERE TP.GRANTEE = RP.GRANTED_ROLE AND TP.OWNER='Schema Name'<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">ORDER BY 1,2,3 <o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Individual Grants given to objects without using Roles:<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">********************************************************<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">SELECT TP.OWNER,TP.GRANTEE,TP.TABLE_NAME, TP.PRIVILEGE FROM dba_tab_privs TP<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">where TP.OWNER='<Schema Name>' AND GRANTEE NOT IN (Select ROLE from DBA_ROLES) ORDER BY 2,3,4<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Thanks & Regards<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Fauzi</span><span style="color:#1F497D"><o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-42039752149686706372013-11-17T02:08:00.001-08:002013-11-17T02:08:35.289-08:00Oracle: History of SQL Statements in Oracle 11g<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Hello,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">The following sql query is very much help full to get the history of sql queries run on the server.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">SQL: <o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">select a.USER_ID, a.SESSION_ID,b.sql_text from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b where a.sql_id=b.sql_id<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Thanks & Regards</span><b><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D"><o:p></o:p></span></b></p> <p class="MsoNormal"><b><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">Fauzi<o:p></o:p></span></b></p> <p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-36571509925911329342013-11-11T23:33:00.001-08:002013-11-11T23:33:50.166-08:00Oralce Query to Get Last Sunday or Frieday of the month<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">The following query is very much useful: <o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">SELECT</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">next_day(last_day(sysdate)-</span><span style="font-size:10.0pt;font-family:Courier;color:maroon;background:white;mso-highlight:white">7</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'Friday'</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> dual</span><span style="font-size:10.0pt;font-family:Courier;color:black"><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:black"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">SELECT</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">next_day(last_day(sysdate)-</span><span style="font-size:10.0pt;font-family:Courier;color:maroon;background:white;mso-highlight:white">7</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'Sunday'</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> dual</span><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Thanks & Regards<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Fauzi</span><span style="color:#1F497D"><o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-90608959483226873282013-05-02T01:41:00.001-07:002013-05-02T01:41:55.198-07:00Oracle: Steps to recover when Redo Files are Lost<div dir="ltr"><div class="gmail_quote"> <div dir="ltr"> <p><br>RCAT>>select name from v$database;</p> <p>NAME<br>---------<br>RCAT</p> <p>RCAT>>alter system set db_recovery_file_dist='+DAT' scope=both;<br>alter system set db_recovery_file_dist='+DAT' scope=both<br> *<br>ERROR at line 1:<br>ORA-02065: illegal option for ALTER SYSTEM</p> <p><br>RCAT>>ed<br>Wrote file afiedt.buf</p> <p> 1* alter system set db_recovery_file_dist='+DATA' scope=both<br>RCAT>>/<br>alter system set db_recovery_file_dist='+DATA' scope=both<br> *<br>ERROR at line 1:<br>ORA-02065: illegal option for ALTER SYSTEM</p> <p><br>RCAT>>ed<br>Wrote file afiedt.buf</p> <p> 1* alter system set db_recovery_file_dest='+DATA' scope=both<br>RCAT>>/<br>alter system set db_recovery_file_dest='+DATA' scope=both<br>*<br>ERROR at line 1:<br>ORA-02097: parameter cannot be modified because specified value is invalid<br> ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE</p> <p><br>RCAT>>ed<br>Wrote file afiedt.buf</p> <p> 1* alter system set db_recovery_file_dest_size=10M scope=both<br>RCAT>>/</p> <p>System altered.</p> <p>RCAT>>/ed<br>SP2-0042: unknown command "/ed" - rest of line ignored.<br>RCAT>>ed<br>Wrote file afiedt.buf</p> <p> 1* alter system set db_recovery_file_dest='+DAT' scope=both<br>RCAT>>/</p> <p>System altered.</p> <p>RCAT>>archive log list;<br>Database log mode No Archive Mode<br>Automatic archival Disabled<br>Archive destination USE_DB_RECOVERY_FILE_DEST<br>Oldest online log sequence 1<br> Current log sequence 2<br>RCAT>>shutdown immediate;<br>Database closed.<br>Database dismounted.<br>ORACLE instance shut down.<br>RCAT>>startup mount<br>ORACLE instance started.</p> <p>Total System Global Area 626327552 bytes<br>Fixed Size 2178336 bytes<br>Variable Size 390071008 bytes<br>Database Buffers 230686720 bytes<br>Redo Buffers 3391488 bytes<br> Database mounted.<br>RCAT>>shutdown immediate;<br>ORA-01109: database not open</p> <p><br>Database dismounted.<br>ORACLE instance shut down.<br>RCAT>>startuo<br>SP2-0042: unknown command "startuo" - rest of line ignored.<br>RCAT>>startup<br>ORACLE instance started.</p> <p>Total System Global Area 626327552 bytes<br>Fixed Size 2178336 bytes<br>Variable Size 390071008 bytes<br>Database Buffers 230686720 bytes<br>Redo Buffers 3391488 bytes<br> Database mounted.<br>ORA-03113: end-of-file on communication channel<br>Process ID: 1896<br>Session ID: 1 Serial number: 5</p> <p><br>RCAT>>startup nomount<br>ORA-24324: service handle not initialized<br>ORA-01041: internal error. hostdef extension doesn't exist<br>RCAT>>shutdown<br>ORA-24324: service handle not initialized<br>ORA-01041: internal error. hostdef extension doesn't exist<br> RCAT>>startup nomount<br>ORA-24324: service handle not initialized<br>ORA-01041: internal error. hostdef extension doesn't exist<br>RCAT>>shutdown immediate<br>ORA-24324: service handle not initialized<br> ORA-01041: internal error. hostdef extension doesn't exist<br>RCAT>>exit<br>Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64<br>bit Production<br>With the Partitioning, OLAP, Data Mining and Real Application Testing options</p> <p>C:\Users\Administrator><br>C:\Users\Administrator><br>C:\Users\Administrator>sqlplus / as sysdba</p> <p>SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:03 2013</p> <p>Copyright (c) 1982, 2010, Oracle. All rights reserved.</p> <p>Connected to an idle instance.</p> <p>SQL> select * from v$instance<br> 2 ;<br>select * from v$instance<br>*<br>ERROR at line 1:<br>ORA-01034: ORACLE not available<br>Process ID: 0<br>Session ID: 0 Serial number: 0</p> <p><br>SQL> exit<br>Disconnected</p> <p>C:\Users\Administrator>set oracle_sid=rcat</p> <p>C:\Users\Administrator>sqlplus / as sysdba</p> <p>SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:42 2013</p> <p>Copyright (c) 1982, 2010, Oracle. All rights reserved.</p> <p>Connected to an idle instance.</p> <p>SQL> startup mount<br>ORACLE instance started.</p> <p>Total System Global Area 626327552 bytes<br>Fixed Size 2178336 bytes<br>Variable Size 390071008 bytes<br>Database Buffers 230686720 bytes<br>Redo Buffers 3391488 bytes<br> ORA-03113: end-of-file on communication channel<br>Process ID: 3208<br>Session ID: 1 Serial number: 3</p> <p><br>SQL> exit<br>Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64<br>bit Production<br>With the Partitioning, OLAP, Data Mining and Real Application Testing options</p> <p>C:\Users\Administrator>set oracle_sid=rcat</p> <p>C:\Users\Administrator>sqlplus / as sysdba</p> <p>SQL*Plus: Release 11.2.0.1.0 Production on Thu May 2 12:31:59 2013</p> <p>Copyright (c) 1982, 2010, Oracle. All rights reserved.</p> <p>Connected to an idle instance.</p> <p>SQL> startup nomount<br>ORACLE instance started.</p> <p>Total System Global Area 626327552 bytes<br>Fixed Size 2178336 bytes<br>Variable Size 390071008 bytes<br>Database Buffers 230686720 bytes<br>Redo Buffers 3391488 bytes<br> SQL> alter database mount ;</p> <p>Database altered.</p> <p>SQL> alter database clear logfile group 1;</p> <p>Database altered.</p> <p>SQL> alter database clear logfile group 2;</p> <p>Database altered.</p> <p>SQL> alter database clear logfile group 3;</p> <p>Database altered.</p> <p>SQL> alter database clear unarchived logfile group 1;</p> <p>Database altered.</p> <p>SQL> alter database clear unarchived logfile group 2;</p> <p>Database altered.</p> <p>SQL> alter database clear unarchived logfile group 3;</p> <p>Database altered.</p> <p>SQL></p> <div dir="ltr"> <div> <div> <div><br></div></div></div></div></div></div><br><br clear="all"><br>-- <br> <div dir="ltr"><font color="#999999"><b>Fauzi<br>+965-97324246</b></font><br><a href="mailto:vm.mohammedfauzi@gmail.com" target="_blank"><img src="http://www.websignature.in/websign/111019/1319052013.gif"></a> <a href="mailto:mohammedfauzi_vm@yahoo.com" target="_blank"><img src="http://www.websignature.in/websign/111019/1319052316.gif"></a> <a href="http://mohammedfauzi.blogspot.com/" target="_blank"><img src="http://www.websignature.in/websign/111019/1319052509.gif"></a> <div> <div><br> <div><br></div></div></div></div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-68101416786167570302013-05-01T23:52:00.000-07:002013-05-01T23:53:12.591-07:00Oracle : Recovery Steps when control file is lost. If backup was done<div dir="ltr"><p><br clear="all">SQL> startup;<br>ORACLE instance started.</p> <p>Total System Global Area 626327552 bytes<br>Fixed Size 2178336 bytes<br>Variable Size 390071008 bytes<br>Database Buffers 230686720 bytes<br>Redo Buffers 3391488 bytes<br> ORA-00205: error in identifying control file, check alert log for more info</p> <p><br>SQL> select status fron v$instance;<br>select status fron v$instance<br> *<br>ERROR at line 1:<br>ORA-00923: FROM keyword not found where expected</p> <p><br>SQL> ed<br>Wrote file afiedt.buf</p> <p> 1* select status from v$instance<br>SQL> /</p> <p>STATUS<br>------------<br>STARTED</p> <p>SQL> show parameter control_files</p> <p>NAME TYPE VALUE<br>------------------------------------ ----------- ------------------------------<br>control_files string F:\RCAT\CONTROL01.CTL, F:\RCAT<br> \CONTROL02.CTL<br>SQL> startup<br>ORA-01081: cannot start already-running ORACLE - shut it down first<br>SQL><br>SQL> shutdown<br>ORA-01507: database not mounted</p> <p><br>ORACLE instance shut down.<br>SQL> startup<br>ORACLE instance started.</p> <p>Total System Global Area 626327552 bytes<br>Fixed Size 2178336 bytes<br>Variable Size 390071008 bytes<br>Database Buffers 230686720 bytes<br>Redo Buffers 3391488 bytes<br> Database mounted.<br>ORA-01589: must use RESETLOGS or NORESETLOGS option for database open</p> <p><br>SQL> alter database open resetlogs;<br>alter database open resetlogs<br>*<br>ERROR at line 1:<br>ORA-01152: file 1 was not restored from a sufficiently old backup<br>ORA-01110: data file 1: 'F:\RCAT\SYSTEM01.DBF'</p> <p><br>SQL> recover database using backup controlfile;<br>ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1<br>ORA-00289: suggestion :<br>C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001<br> ORA-00280: change 967649 for thread 1 is in sequence #3</p> <p><br>Specify log: {<RET>=suggested | filename | AUTO | CANCEL}<br>F:\rcat\redo01.log<br>ORA-00328: archived log ends at change 950490, need later change 967649<br>ORA-00334: archived log: 'F:\RCAT\REDO01.LOG'</p> <p><br>SQL> select * from v$log ;</p> <p> GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC<br>---------- ---------- ---------- ---------- ---------- ---------- ---<br>STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME<br>---------------- ------------- --------- ------------ ---------<br> 1 1 1 52428800 512 1 NO<br>INACTIVE 947455 01-MAY-13 950491 01-MAY-13</p> <p> 3 1 3 52428800 512 1 NO<br>CURRENT 959675 01-MAY-13 2.8147E+14</p> <p> 2 1 2 52428800 512 1 NO<br>INACTIVE 950491 01-MAY-13 959675 01-MAY-13</p> <p><br>SQL> recover database using backup controlfile;<br>ORA-00279: change 967649 generated at 05/01/2013 10:32:22 needed for thread 1<br>ORA-00289: suggestion :<br>C:\ORACLEBASE\DATABASE\RDBMS\ARC0000000003_0814268715.0001<br> ORA-00280: change 967649 for thread 1 is in sequence #3</p> <p><br>Specify log: {<RET>=suggested | filename | AUTO | CANCEL}<br>F:\rcat\redo03.log<br>Log applied.<br>Media recovery complete.<br>SQL> alter database open resetlogs;</p> <p>Database altered.</p> <p>SQL> select * from v$log;</p> <p> GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC<br>---------- ---------- ---------- ---------- ---------- ---------- ---<br>STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME<br>---------------- ------------- --------- ------------ ---------<br> 1 1 1 52428800 512 1 NO<br>CURRENT 972032 02-MAY-13 2.8147E+14</p> <p> 2 1 0 52428800 512 1 YES<br>UNUSED 0 0</p> <p> 3 1 0 52428800 512 1 YES<br>UNUSED 0 0</p> <p><br>SQL> set sqlprompt RCAT>><br>RCAT>><br>-- <br></p> <div dir="ltr"><font color="#999999"><b>Fauzi<br>+965-97324246</b></font><br><a href="mailto:vm.mohammedfauzi@gmail.com" target="_blank"><img src="http://www.websignature.in/websign/111019/1319052013.gif"></a> <a href="mailto:mohammedfauzi_vm@yahoo.com" target="_blank"><img src="http://www.websignature.in/websign/111019/1319052316.gif"></a> <a href="http://mohammedfauzi.blogspot.com/" target="_blank"><img src="http://www.websignature.in/websign/111019/1319052509.gif"></a> <div> <div><br> <div><br></div></div></div></div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-34476437589271447002013-05-01T03:04:00.001-07:002013-05-01T03:04:59.551-07:00Oracle: To create spfile copy<div dir="ltr"><div>From SP file:</div> <div> </div> <div>SYS>>create pfile='f:\oracpara.ora' from spfile;</div> <div>File created.</div> <div> </div> <div>From Memory:</div> <div>SYS>>create pfile='f:\oracpara.ora' from memory;</div> <div>File created.</div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-26334545794750979162013-05-01T01:31:00.000-07:002013-05-01T01:32:19.989-07:00Oracle: To run a structural change sql statement from RMAN:<div dir="ltr"><div>To run a structural change sql statement from RMAN:</div> <div> </div> <div>RMAN> sql 'alter database enable block change tracking'<br>2> ;</div> <div dir="ltr"> <div> <div><br> <div><br></div></div></div></div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-68541433072138813422013-04-25T04:36:00.001-07:002013-04-25T04:36:41.857-07:00Oracle : SQL to find in Top Activity in EM<div dir="ltr"><div>Hello,</div> <div> </div> <div>The following SQL from default schema is useful to create TOP activity in Server,</div> <p><strong>select * from sh.sales,sh.customers, sh.channels</strong></p> <div>Note: Only for testing server :)</div> <div> </div> <div>It was helpful to find it in Enterprise Manager.</div> <div><font color="#999999"><b></b></font> </div> <div><br> </div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-48751169638534245852013-04-23T22:37:00.001-07:002013-04-23T22:37:25.663-07:00Oracle: To Know Current System Change No.<div dir="ltr"><div> To Know Current System Change No. :</div> <div> </div> <div> select Current_scn from v$database;<br clear="all"><br></div> <div dir="ltr"> <div> <div> <div><br></div></div></div></div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-54539303676381957492013-04-22T03:07:00.001-07:002013-04-22T03:07:27.435-07:00Oracle: How to lock a table with select command<div dir="ltr"><div>How to lock a table with select command:</div> <div> </div> <div>SQL: select * from scott.dept for update;<br clear="all"><br>Remember to Rollback when you want to unlock later :)</div></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-69449047512802370682013-04-22T01:56:00.001-07:002013-04-22T01:56:35.003-07:00Oracle: Best Practice for TABLES & INDEXES Tablespace<div dir="ltr"><p> <table dir="ltr" border="0" cellspacing="0" cellpadding="2" width="808"> <tbody> <tr> <td height="32" width="25%" colspan="3"><font face="Arial"><font face="Arial"> <p align="left">Tip of the day: Best Practice (for realtime): </p></font></font></td></tr></tbody></table></p> <table><td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <td height="32" width="8%"> <p></p></td> <tr> <td height="32" colspan="12"><font face="Arial"><font face="Arial"> <p align="left">Create tablespaces for TABLES, and separate tablespaces </p></font></font></td></tr> <tr> <td height="32" width="92%" colspan="11"><font face="Arial"><font face="Arial"> <p align="left">for INDEXES for better performance and management.</p></font></font></td> <td height="32" width="8%"> <p></p></td></tr></table></div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-74571223714149747012013-02-12T03:58:00.001-08:002013-02-12T03:58:43.660-08:00Oracle 8i : DB Links script with credentials<div class="WordSection1"> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">The Following script is very much helpful to generate script for DB Links created in the server.<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">SELECT</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'create '</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">DECODE(U.NAME,</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'PUBLIC'</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'public '</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'database link '</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">CHR(</span><span style="font-size:10.0pt;font-family:Courier;color:maroon;background:white;mso-highlight:white">10</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">DECODE(U.NAME,</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'PUBLIC'</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,Null,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">U.NAME</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'.'</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">|| L</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.NAME</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">chr(</span><span style="font-size:10.0pt;font-family:Courier;color:maroon;background:white;mso-highlight:white">10</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">'connect to '</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> || L</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">USERID || </span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">' identified by '</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||L</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.PASSWORD</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">' using '''</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> || L</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.host</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> || </span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">''''</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">chr(</span><span style="font-size:10.0pt;font-family:Courier;color:maroon;background:white;mso-highlight:white">10</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">||</span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">';'</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> TEXT<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">FROM</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> sys</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">link$ L</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> sys</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">user$ </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">U</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">WHERE</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> L</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">OWNER# </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">=</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">U.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">USER#</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">;</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-61940950726613465762012-12-20T02:51:00.000-08:002012-12-20T02:52:16.602-08:00<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><a href="http://www.google.com/search?q=kuwait+weather&sourceid=ie7&rls=com.microsoft:en-us:IE-SearchBox&ie=&oe">http://www.google.com/search?q=kuwait+weather&sourceid=ie7&rls=com.microsoft:en-us:IE-SearchBox&ie=&oe</a>=<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqRpDdylc7P6MSp7g6zjYrAPsujBUxrR6AsGhLz6QP_vlR68RRfLJgGz55v4oH1wwbDaTKeyCaGFU4mvslw6ILNOMOindcUYInOqSlHr_f5E2k5Y_2NOfHf8rmRxOrNLJe9V53iz7XLGvI/s1600/image001-736602.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqRpDdylc7P6MSp7g6zjYrAPsujBUxrR6AsGhLz6QP_vlR68RRfLJgGz55v4oH1wwbDaTKeyCaGFU4mvslw6ILNOMOindcUYInOqSlHr_f5E2k5Y_2NOfHf8rmRxOrNLJe9V53iz7XLGvI/s320/image001-736602.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5823978815132471410" /></a><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Thanks & Regards<o:p></o:p></span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style="border-collapse:collapse"> <tbody> <tr> <td width="75" valign="top" style="width:56.25pt;padding:0in 0in 0in 0in"> <p class="MsoNormal"><span style="color:#1F497D"><br> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPacTDxs_cTe_wyvBpFy3vrWydSOTAIEKlaUHyv7P4BvzynvOnCQnM4YKeBzfcaP4tqni7WQFgIJGQKPmaTPKMJJEmRQB2aLtF8cpG8CBePrTkczO-RjJU2bkAE9ma6MYqYi8x6NUoj_H3/s1600/image002-738658.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPacTDxs_cTe_wyvBpFy3vrWydSOTAIEKlaUHyv7P4BvzynvOnCQnM4YKeBzfcaP4tqni7WQFgIJGQKPmaTPKMJJEmRQB2aLtF8cpG8CBePrTkczO-RjJU2bkAE9ma6MYqYi8x6NUoj_H3/s320/image002-738658.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5823978824647615938" /></a></span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> <td style="padding:0in 0in 0in 0in"> <table class="MsoNormalTable" border="0" cellpadding="0"> <tbody> <tr> <td style="padding:.75pt .75pt .75pt .75pt"> <p class="MsoNormal"><b><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">Varusai Fauzi Mohammed</span></b><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> <tr> <td style="padding:.75pt .75pt .75pt .75pt"> <p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">|Kuwait Oil Company (KOC) |</span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> <tr> <td style="padding:.75pt .75pt .75pt .75pt"> <p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">|Systems Analyst | Research & Technology |</span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> <tr> <td style="padding:.75pt .75pt .75pt .75pt"> <p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">|Email: </span><span style="font-size:9.0pt;font-family:"Segoe UI","sans-serif";color:black"><a href="mailto:FVARUSAI@kockw.com"><span style="color:blue">FVARUSAI@kockw.com</span></a> </span><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">| Web: <a href="http://www.kockw.com/"><span style="color:blue">www.kockw.com</span></a> |</span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> <tr> <td style="padding:.75pt .75pt .75pt .75pt"> <p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">|Tel: +965 238 72496 | Fax: +965 23982370 |</span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> <tr> <td style="padding:.75pt .75pt .75pt .75pt"> <p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Tahoma","sans-serif";color:#7D7D7D">|P.O Box 9758 | Ahmadi | Postal Code 61008 | Kuwait |</span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> </tbody> </table> <p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span style="font-size:7.5pt;font-family:"Tahoma","sans-serif";color:#00B050"> </span><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:#00B050">Please consider the environment before printing</span><span style="color:#1F497D"><br> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyu4MKRXEmdnmG2mzuochbqyEz5kfIkg3Sv7qdbYhj8yDtKZ0ZiN_meIQagYKJJC5CjKTkTXiADVFO2ft77JSjYWBnHO1xkOvPlvxT4VslteO7MRt2p5FEsLCVbpYDme0fDjNxLJKCUAxE/s1600/image003-741609.jpg"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyu4MKRXEmdnmG2mzuochbqyEz5kfIkg3Sv7qdbYhj8yDtKZ0ZiN_meIQagYKJJC5CjKTkTXiADVFO2ft77JSjYWBnHO1xkOvPlvxT4VslteO7MRt2p5FEsLCVbpYDme0fDjNxLJKCUAxE/s320/image003-741609.jpg" border="0" alt="" id="BLOGGER_PHOTO_ID_5823978836318839762" /></a></span><span style="font-size:12.0pt;color:#1F497D"><o:p></o:p></span></p> </td> </tr> </tbody> </table> <p class="MsoNormal"><span style="color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-11854932133917283742012-11-05T01:35:00.001-08:002012-11-05T01:35:25.855-08:00Oracle: syntax to Alter materialized view<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Alter materialized view MV_NAME<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">refresh complete<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">start with trunc(sysdate)+17/24<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">next trunc(sysdate) - to_number(to_char(sysdate - 1, 'd')) + 5;<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Thanks & Regards<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Fauzi</span><span style="color:#1F497D"><o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-44244143036345656702012-10-30T22:01:00.001-07:002012-10-30T22:01:46.117-07:00SQL to list the number of database files in Oracle Database<div class="WordSection1"> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue;background:white;mso-highlight:white">Hi there,<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue;background:white;mso-highlight:white">Found this interesting query which will list you all the DB files holding your database:<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">select</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">name,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> bytes<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">(select</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">name,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> bytes<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> v$datafile<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">union</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">all</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">select</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">name,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> bytes<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> v$tempfile<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">union</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">all</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">select</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> lf</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.member</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">"name"</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> l</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">bytes<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> v$logfile lf<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> v$log l<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">where</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> lf</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">group# </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">=</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> l</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">group#</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> used<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">,</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">(select</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">sum(</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">bytes</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">as</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> poo<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">from</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> dba_free_space</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">)</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> free<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">/</span><span style="font-size:10.0pt;font-family:Courier;color:blue"><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue">Hope it helps,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue">Regards<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:blue">Fauzi</span><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p></o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-49750471303836183172012-10-11T03:18:00.000-07:002012-10-11T03:19:25.416-07:00How to select distinct Columns from DataTable C#<div class="WordSection1"> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:#2B91AF">Following is the code helpful to filter distinct columns from a DataTable<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:#2B91AF"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:#2B91AF">DataTable</span><span style="font-size:9.5pt;font-family:Consolas"> distinctDT = dtSOURCE.DefaultView.ToTable(<span style="color:#A31515">"TAB_NAME"</span>, <span style="color:blue">true</span>, <span style="color:#A31515">"YourCOLUMN"</span>);<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas">Time to rush back to Visual Studio Bye </span><span style="font-size:9.5pt;font-family:Wingdings">J</span><span style="font-size:9.5pt;font-family:Consolas"><o:p></o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-59489632309408166422012-04-30T01:46:00.001-07:002012-04-30T01:46:28.169-07:00Oracle: To Display list of table spaces<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">FROM sys.dba_free_space fs, sys.dba_data_files dd<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">WHERE dd.tablespace_name = fs.tablespace_name<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">AND dd.file_id = fs.file_id<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">ORDER BY dd.tablespace_name, dd.file_name;</span><o:p></o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-79526562442847294772012-04-09T04:52:00.000-07:002012-04-09T04:53:01.611-07:00Oracle SQL Thread: Convert String to Datetime<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Found this link useful <o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><a href="https://forums.oracle.com/forums/thread.jspa?threadID=598694">https://forums.oracle.com/forums/thread.jspa?threadID=598694</a><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Courier New";color:black">select to_date('2007-01-01 12:00:01 AM','yyyy-mm-dd hh:mi:ss pm') as dt from dual;<br> <br> <o:p></o:p></span></p> <p class="MsoNormal"><o:p> </o:p></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-47737663327040870792012-03-28T05:37:00.001-07:002012-03-28T05:37:23.527-07:00ORA-22858: invalid alteration of datatype, ORA-22858: invalid alteration of datatype<div class="WordSection1"> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">When we try to modify a datatype from varchar2(400) to <b>Clob</b> in a Oracle table, we’ll get the following error:<o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><u><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">The following steps is the work-around to solve this issue:<o:p></o:p></span></u></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">ALTER</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">TABLE</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> SCHEMANAME</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">TABLENAME </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">ADD</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> NEWCOLUMN </span><span style="font-size:10.0pt;font-family:Courier;color:red;background:white;mso-highlight:white">CLOB</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">;</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">UPDATE</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> SCHEMANAME</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">TABLENAME </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">SET</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> NEWCOLUMN </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">=</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> OLDCOLUMN</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">;</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">ALTER</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">TABLE</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> SCHEMANAME</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">TABLENAME </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">drop</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">COLUMN</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> OLDCOLUMN</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">;</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p></o:p></span></p> <p class="MsoNormal" style="text-autospace:none"><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">ALTER</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">TABLE</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> SCHEMANAME</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">.</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white">TABLENAME </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">rename</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">COLUMN</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> NEWCOLUMN </span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">TO</span><span style="font-size:10.0pt;font-family:Courier;color:black;background:white;mso-highlight:white"> OLDCOLUMN</span><span style="font-size:10.0pt;font-family:Courier;color:blue;background:white;mso-highlight:white">;</span><span style="font-size:10.0pt;font-family:Courier;color:blue"><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:Courier;color:blue"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-60660985921727578932012-03-28T03:58:00.001-07:002012-03-28T03:58:52.791-07:00To Search for a text in All the files in a folder in Windows 7<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Hello,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">In windows 7 - I want to search for all files containing "A word or phrase in the file". The standard XP search box, But I was not able to get the result until I did the following steps. <o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Hope it is helpful for people who is looking for the same feature.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><a href="http://answers.microsoft.com/en-us/windows/forum/windows_7-files/in-windows-7-i-want-to-search-for-all-files/aadfe1f1-4a33-406b-8e72-bb920efa4f30">http://answers.microsoft.com/en-us/windows/forum/windows_7-files/in-windows-7-i-want-to-search-for-all-files/aadfe1f1-4a33-406b-8e72-bb920efa4f30</a><o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal" style="background:white"><span style="font-size:9.0pt;font-family:"Arial","sans-serif""><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqLzA8e14KsCh0yihhP3IRtKHH56eZ01AOV601qJr5hOATyzuGUdHuxuNB8WVG0tjOhOd7dlZhJRYi6-pm6rhByMlexE6UaJFOBKHl2hpnh1fsS5Iy1vu5xCX22AwpBHl2pzWfmy-lNVb_/s1600/image001-732792.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqLzA8e14KsCh0yihhP3IRtKHH56eZ01AOV601qJr5hOATyzuGUdHuxuNB8WVG0tjOhOd7dlZhJRYi6-pm6rhByMlexE6UaJFOBKHl2hpnh1fsS5Iy1vu5xCX22AwpBHl2pzWfmy-lNVb_/s320/image001-732792.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5724900780064174018" /></a></span><span style="font-size:9.0pt;font-family:"Arial","sans-serif""><o:p></o:p></span></p> <div class="MsoNormal" align="center" style="text-align:center;background:white"> <span style="font-size:9.0pt;font-family:"Arial","sans-serif""> <hr size="2" width="100%" align="center"> </span></div> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0tag:blogger.com,1999:blog-7932991457643330934.post-36858280471949007582012-03-26T04:48:00.000-07:002012-03-26T04:49:03.502-07:00ORA-01461: can bind a LONG value only for insert into a LONG column<div class="WordSection1"> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Salam,<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"> <o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">To this issue showed me stars!<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">We are using Devart dotconnect to connect to oracle database.<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">In the Dataset, in the Table adapter the max length is set to -1 by default. And in the Auto generated Abstract class the string datatype length is set to 1024 by default. Now to fix this if we change the max length column alone will not be sufficient. We need the rebuild the method again and then move the DataSet to production to make the changes come into effect!<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D">Best Regards<o:p></o:p></span></p> <p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif";color:#1F497D"><o:p> </o:p></span></p> </div> Fauzihttp://www.blogger.com/profile/16387063059628170900noreply@blogger.com0