查看Oracle 数据库的每天归档量大小
1、日志位置
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- ------------------------------------------------------------
1 +DATA/mhdb/onlinelog/group_1.256.982084575
1 +FRA/mhdb/onlinelog/group_1.438.982084575
2 +DATA/mhdb/onlinelog/group_2.262.982084577
2 +FRA/mhdb/onlinelog/group_2.260.982084577
3 +DATA/mhdb/onlinelog/group_3.266.982084577
3 +FRA/mhdb/onlinelog/group_3.259.982084579
4 +DATA/mhdb/onlinelog/group_4.261.982086153
4 +FRA/mhdb/onlinelog/group_4.258.982086153
5 +DATA/mhdb/onlinelog/group_5.260.982086155
5 +FRA/mhdb/onlinelog/group_5.257.982086155
6 +DATA/mhdb/onlinelog/group_6.259.982086155
GROUP# MEMBER
---------- ------------------------------------------------------------
6 +FRA/mhdb/onlinelog/group_6.408.982086157
10 +DATA/mhdb/onlinelog/group_10.283.1004716311
10 +FRA/mhdb/onlinelog/group_10.331.1004716313
11 +DATA/mhdb/onlinelog/group_11.282.1004716329
11 +FRA/mhdb/onlinelog/group_11.387.1004716329
12 +DATA/mhdb/onlinelog/group_12.275.1004716333
12 +FRA/mhdb/onlinelog/group_12.330.1004716333
13 +DATA/mhdb/onlinelog/group_13.274.1004716371
13 +FRA/mhdb/onlinelog/group_13.326.1004716371
14 +DATA/mhdb/onlinelog/group_14.289.1004716371
14 +FRA/mhdb/onlinelog/group_14.332.1004716373
GROUP# MEMBER
---------- ------------------------------------------------------------
15 +DATA/mhdb/onlinelog/group_15.288.1004716377
15 +FRA/mhdb/onlinelog/group_15.483.1004716385
24 rows selected.
SQL> select GROUP#,MEMBERS,BYTES/1024/1024,THREAD# from v$log;
GROUP# MEMBERS BYTES/1024/1024 THREAD#
---------- ---------- --------------- ----------
1 2 512 1
2 2 512 1
3 2 512 1
4 2 512 2
5 2 512 2
6 2 512 2
10 2 512 1
11 2 512 1
12 2 512 1
13 2 512 2
14 2 512 2
15 2 512 2
12 rows selected.
2、日志信息
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1001
Next log sequence to archive 1006
Current log sequence 1006
SQL> show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 57270M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
3、每天产生归档的大小
SQL> Select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024)) as m
b from v$archived_loggroup by to_char(completion_time,'yyyy-mm-dd') order by date1 desc;
DATE1 CNT MB
---------- ---------- ----------
2019-06-27 4 268
2019-06-26 6 1599
2019-06-25 6 1658
2019-06-24 4 460
2019-06-23 4 453
2019-06-22 4 398
2019-06-21 16 751
2019-06-20 6 1024
2019-06-19 6 1073
2019-06-18 6 992
2019-06-17 5 1142
DATE1 CNT MB
---------- ---------- ----------
2019-06-16 4 672
2019-06-15 4 710
2019-06-14 4 643
2019-06-13 6 1534
2019-06-12 6 1497
2019-06-11 6 1397
2019-06-10 6 1546
2019-06-09 4 675
2019-06-08 4 661
2019-06-07 1 7
21 rows selected.
SQL> select sum(a.BLOCK_SIZE*a.BLOCKS)/1024/1024 from v$archived_log a ;
SUM(A.BLOCK_SIZE*A.BLOCKS)/1024/1024
------------------------------------
19157.2739
【注】查看数据库数据大小信息：
1、查看所有表空间及表空间大小：
SQL> select tablespace_name ,(sum(bytes) / 1024 / 1024) as MB from dba_data_files group by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
HTTBS_MESPRD 36863.9844
SYSAUX 14336
UNDOTBS1 1024
USERS 2000
HTTBS_ES_DBA 2000
SYSTEM 4096
HTTBS_MESPRD_IDX 10240
MHDB 24576
UNDOTBS2 1024
9 rows selected.
2、查看所有表空间对应的数据文件：
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
SYSTEM +DATA/mhdb/datafile/system.265.982084581
SYSAUX +DATA/mhdb/datafile/sysaux.258.982084619
UNDOTBS1 +DATA/mhdb/datafile/undotbs1.257.982084655
UNDOTBS2 +DATA/mhdb/datafile/undotbs2.271.982084669
USERS +DATA/mhdb/datafile/users.270.982084671
HTTBS_MESPRD +DATA/mhdb/datafile/httbs_mesprd.269.982084937
HTTBS_ES_DBA +DATA/mhdb/datafile/httbs_es_dba.268.982085079
HTTBS_MESPRD_IDX +DATA/mhdb/datafile/httbs_mesprd_idx.264.982085081
MHDB +DATA/mhdb/datafile/mhdb.263.982085179
SYSAUX +DATA/mhdb/datafile/sysaux.284.1004630203
HTTBS_MESPRD +DATA/mhdb/datafile/httbs_mesprd.273.1004630353
MHDB +DATA/mhdb/datafile/mhdb.279.1004630391
12 rows selected.
