Shulou(Shulou.com)06/01 Report--

1. Query tablespace utilization

SQL > set linesize 400

SQL > set pagesize 400

SQL >

SELECT D.TABLESPACE_NAME tablespace name, SPACE "tablespace (MB)", total number of BLOCKS blocks, SPACE-NVL (FREE_SPACE, 0) "used (MB)"

ROUND ((1-NVL (FREE_SPACE, 0) / SPACE) * 100,2) "usage (%)", FREE_SPACE "unused (MB)"

FROM

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE

SUM (BLOCKS) BLOCKS

FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE

FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)

2. Query the detailed occupancy of sysaux table space

SQL >

Col Schema for a25

Col Item for a25

SELECT occupant_name "Item"

Space_usage_kbytes / 1048576 "Space Used (GB)"

Schema_name "Schema"

Move_procedure "Move Procedure"

FROM v$sysaux_occupants

ORDER BY 1

Item Space Used (GB) Schema Move Procedure

AO. 101318359 SYS DBMS_AW.MOVE_AWMETA

AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables

AUTO_TASK. 000915527 SYS

EM. 11517334 SYSMAN emd_maintenance.move_em_tblspc

EM_MONITORING_USER. 001953125 DBSNMP

EXPRESSION_FILTER. 010620117 EXFSYS

JOB_SCHEDULER. 002380371 SYS

LOGMNR. 030822754 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE

LOGSTDBY. 00402832 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE

ORDIM. 001281738 ORDSYS ordsys.ord_admin.move_ordim_tblspc

ORDIM/ORDDATA. 046875 ORDDATA ordsys.ord_admin.move_ordim_tblspc

ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc

ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc

PL/SCOPE. 001831055 SYS

SDO. 14440918 MDSYS MDSYS.MOVE_SDO

SM/ADVISOR. 102294922 SYS

SM/AWR 1.22515869 SYS

SM/OPTSTAT 11.755188 SYS

SM/OTHER. 020568848 SYS

SMON_SCN_TIME. 004394531 SYS

SQL_MANAGEMENT_BASE. 004943848 SYS

STATSPACK 0 PERFSTAT

STREAMS. 002929688 SYS

TEXT. 010437012 CTXSYS DRI_MOVE_CTXSYS

TSM 0 TSMSYS

ULTRASEARCH 0 WKSYS MOVE_WK

ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK

WM. 010253906 WMSYS DBMS_WM.move_proc

XDB. 317932129 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE

XSAMD. 015014648 OLAPSYS DBMS_AMD.Move_OLAP_Catalog

XSOQHIST. 101318359 SYS DBMS_XSOQ.OlapiMoveProc

31 rows selected.

2.1 、

SM/OPTSTAT:

This will store older optimizer statistics.

We have a new feature in Oracle Database 10g that allows you to restore old statistics in case you encounter an execution plan regression when refreshing the data. The default retention time for this data is 31 days. More details on how to use this can be found in Note 452011.1. Strictly speaking, this is not part of AWR, so it is not controlled by AWR retention parameters; instead, we can use the ALTER_STATS_HISTORY_RETENTION procedure of the DBMS_STATS package to set and change the retention time of this data. For example, you can first check the current retention time and then change it to a different quantity (within a few days), such as 10 days:

Select dbms_stats.get_stats_history_retention from dual

Exec dbms_stats.alter_stats_history_retention (10)

If you prefer to purge data specifically before a timestamp, you can use the PURGE_STATS procedure. For example:

Exec DBMS_STATS.PURGE_STATS (to_timestamp_tz ('01-01-2018 00:00:00 Europe/London','DD-MM-YYYY HH24:MI:SS TZR'))

2.2, AWR part

SQL >

Col SNAP_INTERVAL for a20

Col RETENTION for a20

Select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

213111497 + 00000 01 DEFAULT 00lv 00.0 + 00008 00lv 00lv 00.0

Description:

SNAP_INTERVAL=+00000 01GRO 00.0: indicates that the sampling interval is 1 hour

RETENTION=+00008 00.0: indicates that the retention period of sampled data is 8 days

SQL >

Begin

Dbms_workload_repository.modify_snapshot_settings (

Interval = > 60,-once an hour

Retention = > 724060-- keep it for 7 days

Topnsql = > 100

);

End

Note: if there are too many snapshots and take up too much space, you can also delete some snapshots:

Select min (snap_id), max (snap_id) from dba_hist_snapshot;-query minimum and maximum snapshot ID

-Delete snapshots numbered 10758 to 10900

Begin

Dbms_workload_repository.drop_snapshot_range (

Low_snap_id = > 10758,-set on a case-by-case basis

High_snap_id = > 10900

Dbid = > 387090299); DBID according to the actual situation

End

/

Description:

(1) if AWR is not useful, the sampling interval can be adjusted to a maximum of 100 years, and the sampling data retention time can be adjusted to a minimum of 1 day to achieve "disable" AWR.

SQL > BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (

Interval = > 36500mm 24060

Retention = > 1 / 24 / 60)

END

/

Select * from dba_hist_wr_control

DBID SNAP_INTERVAL RETENTION TOPNSQL

--

4134757407 + 36500 36500 DEFAULT 00lv 00.0 + 00001 00lv 00lv 00.0

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.