>  本文主要是收集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

```

【SQL】Oracle資料庫資料量及效能資訊收集

相關文章