tag:blogger.com,1999:blog-2571414265392841828.post276289337118054636..comments2023-07-11T01:54:39.701-07:00Comments on OracleNext - Solution to your Oracle problems: Tablespace Growth History and Forecast for 10g and 11gSalman Ahmed Qureshihttp://www.blogger.com/profile/06946726509853037798noreply@blogger.comBlogger36125tag:blogger.com,1999:blog-2571414265392841828.post-29620452733508351982021-06-10T19:58:50.731-07:002021-06-10T19:58:50.731-07:00You can see above that there a separate code it li...You can see above that there a separate code it listed where you don't need to supply name of the tablespace, and code will provide output for all tablespaces.<br />In order to use as a shell script, use following format. After that you should be able to execute code in a shell script<br /><br /><br />sqlplus /nolog << EOF<br />CONNECT system/PASSWORD<br /><br /><br /><br />EXIT;<br />EOFSalman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-8776959755981877972021-06-10T07:38:21.839-07:002021-06-10T07:38:21.839-07:00I dont want prompt of tablespace_name , i need to ...I dont want prompt of tablespace_name , i need to include ins a script like tbsgrowth.sh and input tablespace _name based on output of tablespace_name of other script , it is possible can you help me Anonymoushttps://www.blogger.com/profile/15429655318884955132noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-47407882865803962202018-09-04T10:14:38.356-07:002018-09-04T10:14:38.356-07:00Hello Salman,
Script is saying that, nothing foun...Hello Salman,<br /><br />Script is saying that, nothing found in AWR report.<br /><br />Will you please help on this.<br /><br />Regards,<br />RohithAnonymoushttps://www.blogger.com/profile/12566171290023107574noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-23292002534990051972018-05-16T20:09:19.790-07:002018-05-16T20:09:19.790-07:00Hi Prabhakar,
Please implement OEM in your environ...Hi Prabhakar,<br />Please implement OEM in your environment to maintain history, or use manual method to store this information into tables. This script is based on AWR and can only manipulate data available in AWR.Salman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-85963523654327177232018-05-14T07:04:16.366-07:002018-05-14T07:04:16.366-07:00Hi Salman,
I wanted total db growth for 3 months,...Hi Salman,<br /><br />I wanted total db growth for 3 months, 6 months, 1 year..I need to sum each table space growth for 3 months, 6 months and 1 year. I don't want even history also. I am adding every day table spaces for each db. I have almost 10 to 15 dbs. it is very difficult for me. if you can give this output, it is very nice of you.<br /><br />Hope you are clear with my reqmt. if not pls do reply<br /><br />VasistaVasistahttps://www.blogger.com/profile/00912962440215064970noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-45415779981869615752018-04-30T19:14:27.274-07:002018-04-30T19:14:27.274-07:00Hello Abhijit,
Sorry for late reply. You would nee...Hello Abhijit,<br />Sorry for late reply. You would need to modify the script. Create a table use "CREATE TABLE TAB_NAME AS " add select statement that is part of above script to get the tablespace info. Then instead of DBMS_OUTPUT, use INSERT INTO TAB_NAME SELECT statement to insert the fetch data directly into the table. It is not as simple as I explained, but you can give it a try.Salman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-55054421633038816712018-03-11T03:36:51.153-07:002018-03-11T03:36:51.153-07:00Hello sir!
I want that report in a table format. w...Hello sir!<br />I want that report in a table format. what should i do?Anonymoushttps://www.blogger.com/profile/03699770361268365399noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-34395805614202013132018-02-06T03:16:41.699-08:002018-02-06T03:16:41.699-08:00Excellent script.
Thanks for sharing.Excellent script.<br />Thanks for sharing.GAURAVhttps://www.blogger.com/profile/12885431311481481784noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-17890556648985356212017-07-23T05:49:30.057-07:002017-07-23T05:49:30.057-07:00Hi Vijay,
This script is related to finding out sp...Hi Vijay,<br />This script is related to finding out space usage history, and doing forecast for future, based on this historical usage trend found in the AWR repository. Autoextend for a datafile is not related to either of these.<br /><br />SalmanSalman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-80558243616229253702017-07-22T11:12:02.363-07:002017-07-22T11:12:02.363-07:00Hi Salman,
The space allocated shown here is base...Hi Salman,<br /><br />The space allocated shown here is based on the current allocated size, can you change the script so that it can take the auto-extend on into consideration.<br /><br />vijayVijayhttps://www.blogger.com/profile/04722532576754273358noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-60354641627327850192017-05-22T20:25:04.417-07:002017-05-22T20:25:04.417-07:00Hi Siddhartha,
Unfortunately I don't have any ...Hi Siddhartha,<br />Unfortunately I don't have any script that could fulfill your requirement, but I think you can just slightly modify this script. create a global temporary table with fields ,tablespace_name, growth_30days, growth_60days, growth_90 days. Then insert into this global temporary table the calculated growth "DBMS_OUTPUT.PUT_LINE('1) Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' MB'||' ('||round(((v_ts_growth/v_numdays)*30)/1024,2)||' GB)');" for each period (30 days, 60 days, 90 days). <br />Then after "END LOOP;", you write a "SELECT INTO" statement to query global temporary table (order by growth rate) and insert into pl/sql variables, and then user dbms_output to display.<br />Salman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-86751360130358893802017-05-22T06:26:17.666-07:002017-05-22T06:26:17.666-07:00Thanks Salman . For this tablespace report , I nee...Thanks Salman . For this tablespace report , I need to get top 10 tablespaces with the highest growth prediction. Is there anything you prepared or can you ?<br /><br />Thanks<br />SidAnonymoushttps://www.blogger.com/profile/13496732485038604884noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-23914296973057475982017-05-18T18:44:30.971-07:002017-05-18T18:44:30.971-07:00Hi Siddharths,
Please see following for full datab...Hi Siddharths,<br />Please see following for full database growth<br />http://salmandba.blogspot.sg/2015/01/database-growth-history-and-forecast.html<br /><br />SalmanSalman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-74709617080006704612017-05-18T11:20:34.962-07:002017-05-18T11:20:34.962-07:00Its a very good script Salman . Appreciate your wo...Its a very good script Salman . Appreciate your work . Do you have any script to monitor Full database growth ?Anonymoushttps://www.blogger.com/profile/13496732485038604884noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-60312217781706919132017-01-10T02:39:52.520-08:002017-01-10T02:39:52.520-08:00Hi,
It would need a lot of time and effort :). For...Hi,<br />It would need a lot of time and effort :). For now, can you use "SET MARKUP HTML ON" on SQLPLUs, and see if it helps.Salman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-80203069141333717602016-12-22T03:41:44.723-08:002016-12-22T03:41:44.723-08:00Hi Salman,
can you create a script like this but ...Hi Salman,<br /><br />can you create a script like this but with an HTML output?<br /><br />Thanks,<br />neljanAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-77336567815521184022016-12-22T03:41:30.986-08:002016-12-22T03:41:30.986-08:00Hi Salman,
can you create a script like this but ...Hi Salman,<br /><br />can you create a script like this but with an HTML output?<br /><br />Thanks,<br />neljanAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-20273321690026902942016-04-17T22:04:58.467-07:002016-04-17T22:04:58.467-07:00Hi Sonu,
Use following query by manually providing...Hi Sonu,<br />Use following query by manually providing tablespace ID and correct block size in calculation (I used 8192 block size size). This query should list the maximum size/used size of your tablespace for each day.<br /><br /><br />select max(TABLESPACE_SIZE)*8192/1024/1024,max(TABLESPACE_USEDSIZE)*8192/1024/1024, trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')) from dba_hist_tbspc_space_usage where tablespace_id=1 group by trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS')) order by 3;Salman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-86625481640649851762016-04-15T01:00:44.326-07:002016-04-15T01:00:44.326-07:00Thanks a lot for useful query. I have one question...Thanks a lot for useful query. I have one question:- Can we extract table space data history by daily basis by this query.Sohan Sharmahttps://www.blogger.com/profile/14163356153497496389noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-31996894648204495222016-04-15T00:59:18.584-07:002016-04-15T00:59:18.584-07:00This comment has been removed by the author.Sohan Sharmahttps://www.blogger.com/profile/14163356153497496389noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-61165532422125368382016-03-14T22:46:30.540-07:002016-03-14T22:46:30.540-07:00Hi Salman, Very Nice script. Thanks a lot for shar...Hi Salman, Very Nice script. Thanks a lot for sharing it. Baskarhttps://www.blogger.com/profile/15416034348562107938noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-50748334820616832892016-02-17T05:21:17.008-08:002016-02-17T05:21:17.008-08:00So awesome, thank you so much Salman. Very helpful...So awesome, thank you so much Salman. Very helpful.Anonymoushttps://www.blogger.com/profile/02692407590591207345noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-59553262329405811462015-12-01T10:14:44.714-08:002015-12-01T10:14:44.714-08:00excellent script, thanksexcellent script, thankssunilkrishnahttps://www.blogger.com/profile/13854708119987666557noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-37229898091482886232015-08-31T03:36:07.560-07:002015-08-31T03:36:07.560-07:00Great scripts, thanks Great scripts, thanks Julyahttps://www.blogger.com/profile/15700847989045113195noreply@blogger.comtag:blogger.com,1999:blog-2571414265392841828.post-46133366471606684592015-08-04T21:15:16.323-07:002015-08-04T21:15:16.323-07:00Hi Sridhar,
As I have mentioned in my post that th...Hi Sridhar,<br />As I have mentioned in my post that this script extracts information from AWR which retains snapshots history of 8 days by default and that is the reason that it will show you information based on last 8 days only. If you want to check growth for 30 days, first you would need to change your retention policy of AWR snapshot. Please see following post for how to do this.<br />http://salmandba.blogspot.sg/2015/08/changing-awr-snapshot-retention-and.htmlSalman Ahmed Qureshihttps://www.blogger.com/profile/06946726509853037798noreply@blogger.com