tablespace growth history oracle 9i - QuestiEssaydatesCom

+0 332 548 954

[email protected]

tablespace growth history oracle 9i



8i |
9i |
10g |
11g |
12c |
13c |
18c |
Misc |
PL/SQL |
SQL |
RAC |
WebLogic |
Linux

Home » Dba » Here

DBA Scripts

Monitoring

  • access.sql

  • active_sessions.sql

  • cache_hit_ratio.sql

  • call_stack.sql

  • code_dep.sql

  • code_dep_distinct.sql

  • code_dep_on.sql

  • code_dep_tree.sql

  • column_defaults.sql

  • controlfiles.sql

  • datafiles.sql

  • db_cache_advice.sql

  • db_info.sql

  • db_links.sql

  • db_links_open.sql

  • db_properties.sql

  • df_free_space.sql

  • directories.sql

  • directory_permissions.sql

  • dispatchers.sql

  • error_stack.sql

  • errors.sql

  • explain.sql

  • file_io.sql

  • fk_columns.sql

  • fks.sql

  • free_space.sql

  • health.sql

  • hidden_parameters.sql

  • high_water_mark.sql

  • hot_blocks.sql

  • identify_trace_file.sql

  • index_extents.sql

  • index_monitoring_status.sql

  • index_partitions.sql

  • index_usage.sql

  • invalid_objects.sql

  • jobs.sql

  • jobs_running.sql

  • large_lob_segments.sql

  • large_segments.sql

  • latch_hit_ratios.sql

  • latch_holders.sql

  • latches.sql

  • library_cache.sql

  • license.sql

  • locked_objects.sql

  • locked_objects_internal.sql

  • logfiles.sql

  • longops.sql

  • lru_latch_ratio.sql

  • max_extents.sql

  • min_datafile_size.sql

  • monitor.sql

  • monitor_memory.sql

  • monitoring_status.sql

  • nls_params.sql

  • non_indexed_fks.sql

  • obj_lock.sql

  • object_privs.sql

  • object_status.sql

  • objects.sql

  • open_cursors.sql

  • open_cursors_by_sid.sql

  • open_cursors_full_by_sid.sql

  • options.sql

  • param_valid_values.sql

  • parameter_diffs.sql

  • parameters.sql

  • parameters_non_default.sql

  • part_tables.sql

  • pga_target_advice.sql

  • pipes.sql

  • profiler_run_details.sql

  • profiler_runs.sql

  • profiles.sql

  • rbs_extents.sql

  • rbs_stats.sql

  • recovery_status.sql

  • recyclebin.sql

  • redo_by_day.sql

  • redo_by_hour.sql

  • redo_by_min.sql

  • registry_history.sql

  • role_privs.sql

  • roles.sql

  • search_source.sql

  • segment_size.sql

  • segment_stats.sql

  • segments_in_ts.sql

  • session_events.sql

  • session_events_by_sid.sql

  • session_events_by_spid.sql

  • session_io.sql

  • session_pga.sql

  • session_rollback.sql

  • session_stats.sql

  • session_stats_by_sid.sql

  • session_undo.sql

  • session_waits.sql

  • sessions.sql

  • sessions_by_machine.sql

  • show_indexes.sql

  • show_space.sql

  • show_tables.sql

  • source.sql

  • spfile_parameters.sql

  • sql_area.sql

  • sql_text.sql

  • sql_text_by_sid.sql

  • statistics_prefs.sql

  • synonyms_to_missing_objects.sql

  • system_events.sql

  • system_parameters.sql

  • system_privs.sql

  • system_stats.sql

  • table_dep.sql

  • table_extents.sql

  • table_indexes.sql

  • table_partitions.sql

  • table_stats.sql

  • table_triggers.sql

  • tables_with_locked_stats.sql

  • tables_with_zero_rows.sql

  • tablespaces.sql

  • temp_extent_map.sql

  • temp_free_space.sql

  • temp_io.sql

  • temp_segments.sql

  • temp_usage.sql

  • tempfiles.sql

  • tempseg_usage.sql

  • top_latches.sql

  • top_sessions.sql

  • top_sql.sql

  • trace_run_details.sql

  • trace_runs.sql

  • ts_datafiles.sql

  • ts_extent_map.sql

  • ts_free_space.sql

  • ts_full.sql

  • ts_thresholds.sql

  • ts_thresholds_reset.sql

  • ts_thresholds_set_default.sql

  • tuning.sql

  • unusable_indexes.sql

  • unused_space.sql

  • user_hit_ratio.sql

  • user_roles.sql

  • user_system_privs.sql

  • user_temp_space.sql

  • user_undo_space.sql

  • users.sql

  • users_with_role.sql

  • users_with_sys_priv.sql

10g

  • active_session_waits.sql

  • ash.sql

  • db_usage_hwm.sql

  • dynamic_memory.sql

  • event_histogram.sql

  • feature_usage.sql

  • flashback_db_info.sql

  • generate_multiple_awr_reports.sql

  • job_chain_rules.sql

  • job_chain_steps.sql

  • job_chains.sql

  • job_classes.sql

  • job_programs.sql

  • job_running_chains.sql

  • job_schedules.sql

  • jobs.sql

  • jobs_running.sql

  • lock_tree.sql

  • scheduler_attributes.sql

  • segment_advisor.sql

  • services.sql

  • session_waits.sql

  • sga_buffers.sql

  • sga_dynamic_components.sql

  • sga_dynamic_free_memory.sql

  • sga_resize_ops.sql

  • sysaux_occupants.sql

  • test_calendar_string.sql

  • window_groups.sql

  • windows.sql

11g

  • admin_privs.sql

  • autotask_change_window_schedules.sql

  • autotask_job_history.sql

  • autotask_schedule.sql

  • default_passwords.sql

  • diag_info.sql

  • extended_stats.sql

  • fda.sql

  • fda_tables.sql

  • fda_ts.sql

  • identify_trace_file.sql

  • job_credentials.sql

  • job_output_file.sql

  • job_run_details.sql

  • memory_dynamic_components.sql

  • memory_resize_ops.sql

  • memory_target_advice.sql

  • network_acl_privileges.sql

  • network_acls.sql

  • result_cache_objects.sql

  • result_cache_report.sql

  • result_cache_statistics.sql

  • result_cache_status.sql

  • session_fix.sql

  • statistics_global_prefs.sql

  • system_fix.sql

  • system_fix_count.sql

  • temp_free_space.sql

12c

  • cdb_resource_plan_directives.sql

  • cdb_resource_plans.sql

  • cdb_resource_profile_directives.sql

  • credentials.sql

  • host_aces.sql

  • host_acls.sql

  • lockdown_profiles.sql

  • login.sql

  • pdbs.sql

  • plugin_violations.sql

  • priv_captures.sql

  • redaction_columns.sql

  • redaction_policies.sql

  • redaction_value_defaults.sql

  • services.sql

18c

  • lockdown_rules.sql

Constraints

  • disable_chk.sql

  • disable_fk.sql

  • disable_pk.sql

  • disable_ref_fk.sql

  • enable_chk.sql

  • enable_fk.sql

  • enable_pk.sql

  • enable_ref_fk.sql

Miscellaneous

  • analyze_all.sql

  • base64decode.sql

  • base64encode.sql

  • blob_to_clob.sql

  • clob_to_blob.sql

  • column_comments.sql

  • comments.sql

  • compile_all.sql

  • compile_all_bodies.sql

  • compile_all_funcs.sql

  • compile_all_procs.sql

  • compile_all_specs.sql

  • compile_all_trigs.sql

  • compile_all_views.sql

  • conversion_api.sql

  • csv.sql

  • date_api.sql

  • dict_comments.sql

  • digest_auth_api.sql

  • drop_all.sql

  • dsp.pkb

  • dsp.pks

  • err.pkb

  • err.pks

  • find_object.sql

  • ftp.pkb

  • ftp.pks

  • gen_health.sql

  • get_pivot.sql

  • get_stat.sql

  • login.sql

  • part_hv_to_date.sql

  • proc_defs.sql

  • rebuild_index.sql

  • smart_quotes_api.sql

  • soap_api.sql

  • string_agg.sql

  • string_api.sql

  • switch_schema.sql

  • table_comments.sql

  • table_defs.sql

  • table_differences.sql

  • trc.pkb

  • trc.pks

  • ts_move_api.sql

Real Application Clusters (RAC)

  • locked_objects_rac.sql

  • longops_rac.sql

  • monitor_memory_rac.sql

  • session_undo_rac.sql

  • session_waits_rac.sql

  • sessions_rac.sql

Resource Manager

  • active_plan.sql

  • consumer_group_usage.sql

  • consumer_groups.sql

  • plan_directives.sql

  • resource_plans.sql

Script Creation

  • backup.sql

  • build_api.sql

  • build_api2.sql

  • create_data.sql

  • db_link_ddl.sql

  • directory_ddl.sql

  • drop_cons_on_table.sql

  • drop_fks_on_table.sql

  • drop_fks_ref_table.sql

  • drop_indexes.sql

  • fks_on_table_ddl.sql

  • fks_ref_table_ddl.sql

  • index_monitoring_off.sql

  • index_monitoring_on.sql

  • logon_as_user.sql

  • logon_as_user_orig.sql

  • monitoring_off.sql

  • monitoring_on.sql

  • network_acls_ddl.sql

  • object_grants.sql

  • profile_ddl.sql

  • rbs_structure.sql

  • recreate_table.sql

  • role_ddl.sql

  • sequence_ddl.sql

  • synonym_by_object_owner_ddl.sql

  • synonym_ddl.sql

  • synonym_public_remote_ddl.sql

  • table_constraints_ddl.sql

  • table_ddl.sql

  • table_grants_ddl.sql

  • table_indexes_ddl.sql

  • table_triggers_ddl.sql

  • tablespace_ddl.sql

  • tablespace_structure.sql

  • trigger_ddl.sql

  • user_ddl.sql

  • view_ddl.sql

Security

  • grant_delete.sql

  • grant_execute.sql

  • grant_insert.sql

  • grant_select.sql

  • grant_update.sql

  • package_synonyms.sql

  • schema_write_access.sql

  • sequence_synonyms.sql

  • table_synonyms.sql

  • view_synonyms.sql

External Scripts

  • MOATS
  • Snapper
  • OraLatencyMap
  • SQL Fiddle

WebLogic

  • deploy_app.py.txt

  • deploy_app.sh

  • start-domain.sh

  • start-forms-reports.sh

  • start-node-manager.sh

  • stop-domain.sh

  • stop-forms-reports.sh

  • stop-node-manager.sh

 

Home |
Articles |
Scripts |
Blog |
Certification |
Misc |
About

About Tim Hall
Copyright & Disclaimer
Privacy Policy

Call now: 252-767-6166 

 

 Home
  E-mail Us

 Oracle

Articles

New Oracle Articles


 Oracle

Training
 Oracle

Tips


 Oracle

Forum
 Class

Catalog


 Remote

DBA
 Oracle

Tuning
 Emergency

911
 RAC

Support
 Apps

Support
 Analysis
 Design
 Implementation
 Oracle

Support


 SQL Tuning
 Security

 Oracle

UNIX
 Oracle

Linux
 Monitoring
 Remote s
upport
 Remote

plans
 Remote

services
 Application

Server

 Applications
 Oracle

Forms
 Oracle

Portal
 App

Upgrades
  SQL

Server
  Oracle Concepts
 Software Support
 Remote

S upport

 

 Development  

 Implementation


 Consulting Staff
 Consulting

Prices
 Help

Wanted!

 

 Oracle Posters
 Oracle Books

 Oracle

Scripts

 Ion

 Excel-DB  

Don Burleson Blog  

 

 

 


 

 

 
 

Oracle tablespace growth
monitoring scripts



Oracle Database Tips by Donald Burleson
November 3, 2015


Question:  I need some scripts for showing
the growth of tablespaces over time periods.  I need to show
the starting tablespace size and the end tablespace size, computing
the tablespace growth for the time period.

Answer:  There are many ways to
compute tablespace growth:

database_growth_reports

tracking_oracle_database_tables_growth

table growth reports

For growth reports tat the tablespace level, we see these
scripts for tablespace growth:

set feed off

column
"tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format
999,999,999.99
column "Free(MB)" heading "Free | (MB)"
format 999,999,999.99
column "Used(MB)" heading "Used |
(MB)" format 999,999,999.99
column "Max Ext(MB)" heading
"Max Ext | (MB)" format 999,999,999
column "%Free"
heading "% | Free" format 999.99
column "%Free Ext"
heading "% | Free Ext" format 999.99
column "Graph"
heading "Graph" format a11
column tablespace_name heading
"Tablespace | Name" format a20

SELECT
  
ts.tablespace_name, "File Count",
  
TRUNC("SIZE(MB)", 2) "Size(MB)",
   TRUNC(fr."FREE(MB)",
2) "Free(MB)",
   TRUNC("SIZE(MB)" –
"FREE(MB)", 2) "Used(MB)",
   df."MAX_EXT" "Max
Ext(MB)",
   (fr."FREE(MB)" / df."SIZE(MB)") *
100 "% Free",
   RPAD(‘*’, TRUNC(CEIL((fr."FREE(MB)"
/ df."SIZE(MB)") * 100)/10), ‘*’)    "Graph"
FROM
   (SELECT tablespace_name,
  
SUM (bytes) / (1024 * 1024) "FREE(MB)"
   FROM
dba_free_space
    GROUP BY
tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes)
/ (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count",
SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM
dba_data_files
GROUP BY tablespace_name) df,
(SELECT
tablespace_name
FROM dba_tablespaces) ts
WHERE
fr.tablespace_name = df.tablespace_name (+)
AND
fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "%
Free" desc
/

prompt
prompt "The last line shows
the Tablespace wich is growing too large…"
prompt


 

dba_hist_seg_stat_usage.sql


****************************************************


— This tablespace growth report
requires AWR license



****************************************************

set feedback on

select * from (select
c.tablespace_name,c.segment_name “object name”,b.object_type,


sum(space_used_delta) / 1024 / 1024
“Growth (MB)”


from
  

see code depot for full tablespace growth script

  
dba_hist_snapshot sn,


   dba_hist_seg_stat a,

   dba_objects b,

   dba_segments c

where

   begin_interval_time >
trunc(sysdate) – &days_back


and

   sn.snap_id = a.snap_id

and

   b.object_id = a.obj#

and

   b.owner = c.owner

and

   b.object_name =
c.segment_name


and

   c.owner =’XXXX’

group by

   c.tablespace_name,

   c.segment_name,

   b.object_type)

order by 3 asc;

dba_hist_tbspc_usage.sql


****************************************************


— This tablespace growth report
requires AWR license



****************************************************


select

   to_char (sp.begin_interval_time,’dd-mm-yyyy’)
days,

  
 ts.tsname,


  
max(round((tsu.tablespace_size* dt.block_size
)/(1024*1024),2) ) cur_size_mb,


   max(round((tsu.tablespace_usedsize*
dt.block_size )/(1024*1024),2)) usedsize_mb,


from


  

see code depot for full tablespace growth script

  
dba_hist_tbspc_space_usage tsu,


   dba_hist_tablespace_stat
ts,

  
dba_hist_snapshot sp,


   dba_tablespaces dt

where

   tsu.tablespace_id= ts.ts#

and

   tsu.snap_id = sp.snap_id

and

   ts.tsname =
dt.tablespace_name


and

   ts.tsname not in (‘SYSAUX’,’SYSTEM’)

group by

   to_char (sp.begin_interval_time,’dd-mm-yyyy’),
ts.tsname


order by ts.tsname, days;



segment_tablespace_growth_report.sql


****************************************************


— This tablespace growth report
requires AWR license



****************************************************

column “Percent of Total Disk Usage”
justify right format 999.99


column “Space Used (MB)” justify right
format 9,999,999.99


column “Total Object Size (MB)” justify
right format 9,999,999.99


set linesize 150

set pages 80

set feedback off

set line 5000

column “SEGMENT_NAME” justify left
format A30


column “TABLESPACE_NAME” justify left format A30

select * from (select c.tablespace_name,c.segment_name,to_char(end_interval_time,
‘MM/DD/YY’) mydate, sum(space_used_delta) / 1024 / 1024
“Space used (MB)”, avg(c.bytes) / 1024 / 1024 “Total Object
Size (MB)”,


round(sum(space_used_delta) / sum(c.bytes) * 100, 2)
“Percent of Total Disk Usage”


from
  


see code depot for full tablespace growth script

  
dba_hist_snapshot sn,


   dba_hist_seg_stat a,

   dba_objects b,

   dba_segments c

where
  
begin_interval_time > trunc(sysdate) – 10


and
   sn.snap_id =
a.snap_id

and

   b.object_id = a.obj#


and
   b.owner = c.owner


and
   b.object_name =
c.segment_name


and
   c.segment_name = ‘S_PARTY’


group by
  
c.tablespace_name,


   c.segment_name,
  
to_char(end_interval_time, ‘MM/DD/YY’)


order by
   c.
tablespace_name,

  
c.segment_name,
   to_date(mydate, ‘MM/DD/YY’));








****************************************************


— This tablespace growth report
requires AWR license



****************************************************

column “percent of total disk usage”
justify right format 999.99


column “space used (mb)” justify right
format 9,999,999.99


column “total object size (mb)” justify
right format 9,999,999.99




column “segment_name” justify left
format a30


column “tablespace_name” justify left format a30

set linesize 150

set pages 80

set feedback off

set line 5000

select * from
(select
c.tablespace_name,c.segment_name,to_char(end_interval_time,
‘mm/dd/yy’) mydate, sum(space_used_delta) / 1024 / 1024
“space used (mb)”, avg(c.bytes) / 1024 / 1024 “total object
size (mb)”,


round(sum(space_used_delta) / sum(c.bytes) * 100, 2)
“percent of total disk usage”


from
  




see code depot for full tablespace growth script


  
dba_hist_snapshot sn,


   dba_hist_seg_stat a,

   dba_objects b,

   dba_segments c

where

   begin_interval_time >
trunc(sysdate) – 10


and

   sn.snap_id = a.snap_id

and

   b.object_id = a.obj#

and

   b.owner = c.owner

and

   b.object_name =
c.segment_name


and

   c.segment_name = ‘s_party’

group by

   c.tablespace_name,

   c.segment_name,

   to_char(end_interval_time,
‘mm/dd/yy’)


order by

  
c.tablespace_name,


   c.segment_name,

   to_date(mydate, ‘mm/dd/yy’));

 

Get the Complete

Oracle SQL Tuning Information 



The landmark book
"Advanced Oracle
SQL Tuning  The Definitive Reference"  is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.


 

 

Burleson is the American Team

Note:

This Oracle

documentation was created as a support and Oracle training reference for use by our

DBA performance tuning consulting professionals. 

Feel free to ask questions on our

Oracle forum .

Verify

experience!

Anyone

considering using the services of an Oracle support expert should

independently investigate their credentials and experience, and not rely on

advertisements and self-proclaimed expertise. All legitimate Oracle experts

publish

their Oracle

qualifications .

Errata?

 Oracle technology is changing and we

strive to update our BC Oracle support information.  If you find an error

or have a suggestion for improving our content, we would appreciate your

feedback.  Just 

e-mail:

 

and include the URL for the page.


                    


Burleson Consulting


The Oracle of

Database Support

Oracle

Performance Tuning

Remote DBA Services


 

Copyright © 1996 –  2017

All rights reserved by

Burleson

Oracle ®

is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by
Conversational

 

 

��

 

 

 

Oracle Training at Sea

 

 

 

 

oracle dba poster

 

 

Follow us on Twitter  

 

Oracle performance tuning software  

 

Oracle Linux poster

 

 
 

 

admin