Oracle database full of modified procedures 20171123

View the use of table space

Select Upper (f.tablespace_name)         "tablespace name"
       d.tot_grootte_mb                 "table space size (M)",
       D.tot_grootte_mb-f.total_ BYTES "used Space (M)",
       To_char (Round (d.tot_grootte_mb-f.total_bytes)/D.TOT_GROOTTE_MB *, 2), ' 990.99 ')
       | | '% '                           "use ratio",
       f.total_bytes                    "free Space (M)",
       f.max_bytes                      "maximum block (m)"
from   (SELECT Tablespace_name,
               Round (Sum (BYTES)/(1024 * 1024), 2 total_bytes,
               Round (Max (BYTES)/(1024 * 1024), 2) Max_ BYTES
        from   SYS. Dba_free_space
        GROUP by  tablespace_name) F,
       (SELECT DD. Tablespace_name,
               Round (Sum (DD). BYTES)/(1024 * 1024), 2) tot_grootte_mb
        from   SYS. Dba_data_files DD
        GROUP by  DD. Tablespace_name) D
WHERE  d.tablespace_name = f.tablespace_name ORDER by  1

To view data files for each table space

Select T1.name, T2.name,round (T2. BYTES/(1024 * 1024), 2) "Size (M)"
  from V$tablespace T1, V$datafile t2
 where t1.ts# = t2.ts# ORDER by
 1,3

To view the use of Sysaux table space

SELECT * FROM
  (select   BYTES, Segment_name, Segment_type, owner from
            dba_segments
           WHERE tablespace_ name = ' Sysaux ' ORDER by
        BYTES DESC)
 WHERE rownum < ' "To

 Modify the

table data file size by executing the following statement

Sql> select dbms_stats.get_stats_history_retention from dual;

Get_stats_history_retention
---------------------------"'
sql> exec dbms_stats.alter_ Stats_history_retention ();
 "'
Pl/sql procedure successfully completed

 '
sql> Select min (snap_id), Max (snap_id) from Dba_hist_ snapshot;
 "MIN" (
snap_id) MAX (snap_id)
------------------------
       35044        35248
 '
sql> EXEC dbms_workload_repository.drop_snapshot_range (low_snap_id =>35044,high_snap_id => 35044+24);
 "'
Pl/sql procedure successfully completed

modify table data file Size '
sql> ALTER DATABASE datafile ' D:\APP \administrator\oradata\xuejiayue\sysaux01. DBF ' RESIZE 2048M;

Database Altered

Sql>

Database Altered

ALTER DATABASE datafile ' D:\APP\ADMINISTRATOR\ORADATA\XUEJIAYUE\UNDOTBS01. DBF ' RESIZE 2048M;

Look at the less efficient statements (http://blog.csdn.net/ocean1010/article/details/5947580)

SELECT executions, disk_reads, Buffer_gets, ROUND ((buffer_gets-disk_reads)/buffer_gets,2) Hit_radio, ROUND (DISK_ reads/executions,2) Reads_per_run, 
sql_text from V$sqlarea WHERE executions>0 and buffer_gets > 0 and (buffer_g Ets-disk_reads)/buffer_gets < 0.8 order by 4 DESC;

And then
Optimization of related tables