查看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.