> 本文主要是收集Oracle数据库大小、类型大小、各用户大小、最大的60个对象、按照表空间增长情况及导出awr元数据。 具体语句如下 ``` -- database size ,object size, object type sze ,top 60 objects,tablespace size,exp awr_data -- -- 'awr_data' default directory /tmp/awrexp -- -- exec command : "sqlplus / as sysdba @oracle_collect2021.sql" --directory !mkdir -p /tmp/awrexp create directory awrexp as '/tmp/awrexp'; col vfilename new_value vfilename select '/tmp/awrexp/db_info'||to_char(sysdate,'yyyymmdd-hh24mi')||'.log' vfilename from dual; spool &vfilename -- db size GB prompt 1.db size_gb: set lines 300 set pages 999 SET TRIMSPOOL ON select trunc(sum(bytes/1024/1024/0124),2) size_GB from dba_segments; --size group by type prompt 2.segment type size_GB: select segment_type,trunc(sum(bytes/1024/1024/0124),2) size_GB from dba_segments group by segment_type; --size group by user prompt 3.user size_GB select owner username,trunc(sum(bytes/1024/1024/0124),2) size_GB from dba_segments group by owner; -- size GB top 60 prompt 4.top 60 object col owner format a15 col Segment_Name format a40 col segment_type format a15 col tablespace_name format a25 col object_name for a30 with temp_select as (select * from(select owner, Segment_Name,segment_type,tablespace_name,trunc(Sum(bytes)/1024/1024/1024,2) as GB From dba_segments group by owner,Segment_Name,segment_type,tablespace_name order by GB desc) where rownum < 61) select * from (select t.*,l.table_name as object_name from temp_select t,dba_lobs l where t.segment_name=l.segment_name union select t.*,t.segment_name as object_name from temp_select t where t.segment_type not in ('LOBSEGMENT')) order by GB desc; --tablespace info prompt 5.11g tablespace size_MB --11g col rtime for a20 SELECT a.snap_id, c.tablespace_name ts_name, to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy/mm/dd hh24:mi') rtime, round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb, round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb, round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024, 2) ts_free_mb, round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used FROM dba_hist_tbspc_space_usage a, (SELECT tablespace_id, substr(rtime, 1, 10) rtime, max(snap_id) snap_id FROM dba_hist_tbspc_space_usage nb group by tablespace_id, substr(rtime, 1, 10)) b, dba_tablespaces c, v$tablespace d where a.snap_id = b.snap_id and a.tablespace_id = b.tablespace_id and a.tablespace_id=d.TS# and d.NAME=c.tablespace_name and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30 order by a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc; --12c prompt 5.12c tablespace size_MB col pdbname for a15 SELECT a.snap_id, a.con_id, e.name pdbname, c.tablespace_name ts_name, to_char(to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd hh24:mi') rtime, round(a.tablespace_size * c.block_size / 1024 / 1024, 2) ts_size_mb, round(a.tablespace_usedsize * c.block_size / 1024 / 1024, 2) ts_used_mb, round((a.tablespace_size - a.tablespace_usedsize) * c.block_size / 1024 / 1024, 2) ts_free_mb, round(a.tablespace_usedsize / a.tablespace_size * 100, 2) pct_used FROM cdb_hist_tbspc_space_usage a, (SELECT tablespace_id, nb.con_id, substr(rtime, 1, 10) rtime, max(snap_id) snap_id FROM dba_hist_tbspc_space_usage nb group by tablespace_id, nb.con_id,substr(rtime, 1, 10)) b, cdb_tablespaces c, v$tablespace d, V$CONTAINERS e where a.snap_id = b.snap_id and a.tablespace_id = b.tablespace_id and a.con_id=b.con_id and a.con_id=c.con_id and a.con_id=d.con_id and a.con_id=e.con_id and a.tablespace_id=d.TS# and d.NAME=c.tablespace_name and to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') >=sysdate-30 order by a.CON_ID,a.tablespace_id,to_date(a.rtime, 'mm/dd/yyyy hh24:mi:ss') desc; --exp awr data prompt 6. exp awr data define dbid = ''; define num_days = ''; define begin_snap = 0; define end_snap = 1; define directory_name = 'AWREXP'; define file_name = ''; col v_filename new_value v_filename noprint col min_snap new_value begin_snap col max_snap new_value end_snap select dbid, max(snap_id) max_snap, min(snap_id) min_snap from dba_hist_snapshot where begin_interval_time >= sysdate - 8 group by dbid; spool off @?/rdbms/admin/awrextr ```