【實驗】通過v$archived_log檢視獲得歸檔日誌檔案的詳細資訊

secooler發表於2009-07-28
Hive
1.查詢方法，SQL語句，這不是重點，重點是在2的status欄位解釋，在不看第二點的說明前，看看聰明的你能不能推斷出字母A,D和X等的含義
sys@ora10g> set lines 200
sys@ora10g> col name for a100
sys@ora10g> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
sys@ora10g> select name,completion_time,status from v$archived_log;

NAME                                                    COMPLETION_TIME     S
------------------------------------------------------- ------------------- -
/archivelog/2009_07_06/o1_mf_1_12_553rlmlp_.arc         2009-07-06 19:39:00 X
/archivelog/2009_07_07/o1_mf_1_13_554lgm7w_.arc         2009-07-07 03:00:35 A
/archivelog/2009_07_07/o1_mf_1_14_555l2t7w_.arc         2009-07-07 12:00:26 A
/archivelog/2009_07_07/o1_mf_1_15_555mvzbo_.arc         2009-07-07 12:30:56 A
/archivelog/2009_07_07/o1_mf_1_16_555mw6v0_.arc         2009-07-07 12:31:03 A
/archivelog/2009_07_07/o1_mf_1_17_555mwvrq_.arc         2009-07-07 12:31:24 A
/archivelog/2009_07_07/o1_mf_1_18_555mxk1k_.arc         2009-07-07 12:31:45 A
/archivelog/2009_07_07/o1_mf_1_19_555my406_.arc         2009-07-07 12:32:04 A
/archivelog/2009_07_07/o1_mf_1_20_555mypvr_.arc         2009-07-07 12:32:23 A
/archivelog/2009_07_07/o1_mf_1_21_555mz9hy_.arc         2009-07-07 12:32:42 A
/archivelog/2009_07_07/o1_mf_1_22_555mzzlh_.arc         2009-07-07 12:33:04 A
/archivelog/2009_07_07/o1_mf_1_23_555n0o84_.arc         2009-07-07 12:33:25 A
/archivelog/2009_07_07/o1_mf_1_24_555n1c9s_.arc         2009-07-07 12:33:48 A
/archivelog/2009_07_07/o1_mf_1_25_555n1qdj_.arc         2009-07-07 12:34:00 A
/archivelog/2009_07_07/o1_mf_1_26_555n3lpc_.arc         2009-07-07 12:34:59 A
/archivelog/2009_07_07/o1_mf_1_27_555n43dp_.arc         2009-07-07 12:35:16 A
/archivelog/2009_07_07/o1_mf_1_28_555n49rw_.arc         2009-07-07 12:35:22 A
/archivelog/2009_07_07/o1_mf_1_29_555n4h03_.arc         2009-07-07 12:35:27 A
/archivelog/2009_07_07/o1_mf_1_30_555n4p7m_.arc         2009-07-07 12:35:34 A
/archivelog/2009_07_07/o1_mf_1_31_555n53dd_.arc         2009-07-07 12:35:48 A
/archivelog/2009_07_07/o1_mf_1_32_555n5ph9_.arc         2009-07-07 12:36:07 A
/archivelog/2009_07_07/o1_mf_1_33_555n5zms_.arc         2009-07-07 12:36:16 A
/archivelog/2009_07_07/o1_mf_1_34_555n680k_.arc         2009-07-07 12:36:24 A
/archivelog/2009_07_07/o1_mf_1_35_555n6gh4_.arc         2009-07-07 12:36:31 A
/archivelog/2009_07_07/o1_mf_1_36_555n6mz4_.arc         2009-07-07 12:36:36 A
/archivelog/2009_07_07/o1_mf_1_37_555n6son_.arc         2009-07-07 12:36:42 A
/archivelog/2009_07_07/o1_mf_1_38_555n7l2b_.arc         2009-07-07 12:37:06 A
/archivelog/2009_07_07/o1_mf_1_39_555n7w5w_.arc         2009-07-07 12:37:16 A
/archivelog/2009_07_07/o1_mf_1_40_555n862s_.arc         2009-07-07 12:37:26 A
/archivelog/2009_07_07/o1_mf_1_41_555n8ot7_.arc         2009-07-07 12:37:42 A
/archivelog/2009_07_07/o1_mf_1_42_555n8zft_.arc         2009-07-07 12:37:52 A
/archivelog/2009_07_07/o1_mf_1_43_555n977o_.arc         2009-07-07 12:37:59 A
/archivelog/2009_07_07/o1_mf_1_44_555n9pp5_.arc         2009-07-07 12:38:15 A
/archivelog/2009_07_07/o1_mf_1_45_555nb0rx_.arc         2009-07-07 12:38:25 A
/archivelog/2009_07_07/o1_mf_1_46_555nbcgd_.arc         2009-07-07 12:38:36 A
/archivelog/2009_07_07/o1_mf_1_47_555nblhp_.arc         2009-07-07 12:38:43 A
/archivelog/2009_07_07/o1_mf_1_48_555nbsz4_.arc         2009-07-07 12:38:50 A
/archivelog/2009_07_07/o1_mf_1_49_555nc0pb_.arc         2009-07-07 12:38:57 A
/archivelog/2009_07_07/o1_mf_1_50_555nc7sg_.arc         2009-07-07 12:39:04 A
/archivelog/2009_07_07/o1_mf_1_51_555ncl1t_.arc         2009-07-07 12:39:14 A
/archivelog/2009_07_07/o1_mf_1_52_555nd5ft_.arc         2009-07-07 12:39:34 A
/archivelog/2009_07_07/o1_mf_1_53_555ndy78_.arc         2009-07-07 12:39:59 A
/archivelog/2009_07_07/o1_mf_1_54_555p2711_.arc         2009-07-07 13:08:24 A
/archivelog/2009_07_07/o1_mf_1_55_556gc237_.arc         2009-07-07 20:02:43 A
/archivelog/2009_07_08/o1_mf_1_56_556zrjkv_.arc         2009-07-08 01:00:01 A
/archivelog/2009_07_09/o1_mf_1_57_55bh76n0_.arc         2009-07-09 08:42:16 A
/archivelog/2009_07_09/o1_mf_1_58_55bh76qd_.arc         2009-07-09 08:42:17 A
/archivelog/2009_07_09/o1_mf_1_59_55bh7b6g_.arc         2009-07-09 08:42:19 A
/archivelog/2009_07_09/o1_mf_1_60_55bh7nwd_.arc         2009-07-09 08:42:29 A
/archivelog/2009_07_10/o1_mf_1_66_55g0bzj5_.arc         2009-07-10 16:52:48 A
/archivelog/2009_07_10/o1_mf_1_67_55gldwk2_.arc         2009-07-10 22:01:01 A
/archivelog/2009_07_11/o1_mf_1_68_55hgh5c7_.arc         2009-07-11 06:00:06 A
/archivelog/2009_07_11/o1_mf_1_69_55j02b44_.arc         2009-07-11 11:00:26 A
/archivelog/2009_07_11/o1_mf_1_70_55jzv2f6_.arc         2009-07-11 20:02:43 A
/archivelog/2009_07_12/o1_mf_1_71_55kzd9no_.arc         2009-07-12 05:00:59 A
/archivelog/2009_07_12/o1_mf_1_72_55lshvjh_.arc         2009-07-12 12:26:36 A
/archivelog/2009_07_12/o1_mf_1_73_55mtof2s_.arc         2009-07-12 21:52:45 A
/archivelog/2009_07_13/o1_mf_1_74_55ns4m3b_.arc         2009-07-13 06:32:51 A
/archivelog/2009_07_13/o1_mf_1_75_55omj805_.arc         2009-07-13 14:02:48 A
/archivelog/2009_07_13/o1_mf_1_76_55phhlv0_.arc         2009-07-13 22:00:19 A
/archivelog/2009_07_14/o1_mf_1_77_55pxlgof_.arc         2009-07-14 02:00:47 A
/archivelog/2009_07_14/o1_mf_1_78_55qwtbko_.arc         2009-07-14 10:54:03 A
/archivelog/2009_07_14/o1_mf_1_79_55rscc79_.arc         2009-07-14 19:00:59 A
/archivelog/2009_07_14/o1_mf_1_80_55s6fcgz_.arc         2009-07-14 22:43:56 A
/archivelog/2009_07_15/o1_mf_1_81_55t0hqcc_.arc         2009-07-15 06:08:56 A
/archivelog/2009_07_15/o1_mf_1_82_55trnf2s_.arc         2009-07-15 13:01:01 A
/archivelog/2009_07_15/o1_mf_1_83_55vr74t8_.arc         2009-07-15 22:00:05 A
/archivelog/2009_07_15/o1_mf_1_84_55vy0cm2_.arc         2009-07-15 23:38:52 A
/archivelog/2009_07_16/o1_mf_1_85_55wxy2vz_.arc         2009-07-16 08:43:47 A
/archivelog/2009_07_16/o1_mf_1_86_55xqjp6n_.arc         2009-07-16 16:00:22 A
/archivelog/2009_07_16/o1_mf_1_87_55ydmr1r_.arc         2009-07-16 22:00:24 A
/archivelog/2009_07_17/o1_mf_1_88_55z030v2_.arc         2009-07-17 03:32:49 A
/archivelog/2009_07_17/o1_mf_1_89_55ztb95d_.arc         2009-07-17 11:00:25 A
/archivelog/2009_07_17/o1_mf_1_90_560pgk7z_.arc         2009-07-17 19:00:34 A
/archivelog/2009_07_17/o1_mf_1_91_56110mcm_.arc         2009-07-17 22:00:52 A
/archivelog/2009_07_18/o1_mf_1_92_561onj3t_.arc         2009-07-18 03:52:48 A
/archivelog/2009_07_18/o1_mf_1_93_56245m86_.arc         2009-07-18 08:00:52 A
/archivelog/2009_07_18/o1_mf_1_94_562wxc6w_.arc         2009-07-18 15:03:07 A
/archivelog/2009_07_18/o1_mf_1_95_563srrj1_.arc         2009-07-18 23:15:37 A
/archivelog/2009_07_19/o1_mf_1_96_564osl49_.arc         2009-07-19 07:13:55 A
/archivelog/2009_07_19/o1_mf_1_97_565j353w_.arc         2009-07-19 14:42:45 A
/archivelog/2009_07_19/o1_mf_1_98_566g5cln_.arc         2009-07-19 23:15:56 A
/archivelog/2009_07_20/o1_mf_1_99_5677rbt3_.arc         2009-07-20 06:32:43 A
/archivelog/2009_07_20/o1_mf_1_100_567tm3wn_.arc        2009-07-20 11:54:12 A
/archivelog/2009_07_20/o1_mf_1_101_568mkx2w_.arc        2009-07-20 19:00:14 A
/archivelog/2009_07_20/o1_mf_1_102_568y4zmo_.arc        2009-07-20 22:01:04 A
/archivelog/2009_07_21/o1_mf_1_103_569nf63k_.arc        2009-07-21 04:20:54 A
/archivelog/2009_07_21/o1_mf_1_104_56bhbphq_.arc        2009-07-21 12:00:23 A
/archivelog/2009_07_21/o1_mf_1_105_56bpdz2f_.arc        2009-07-21 14:01:03 A
/archivelog/2009_07_21/o1_mf_1_106_56bq6bkl_.arc        2009-07-21 14:14:34 A
/archivelog/2009_07_21/o1_mf_1_107_56bqgg4p_.arc        2009-07-21 14:18:54 A
/archivelog/2009_07_21/o1_mf_1_108_56cgytqf_.arc        2009-07-21 21:00:11 A
/archivelog/2009_07_21/o1_mf_1_109_56cpn46m_.arc        2009-07-21 23:11:00 A
/archivelog/2009_07_22/o1_mf_1_110_56dj12mj_.arc        2009-07-22 06:24:35 A
/archivelog/2009_07_22/o1_mf_1_111_56fjv3vx_.arc        2009-07-22 15:44:36 A
/archivelog/2009_07_22/o1_mf_1_112_56g6vbrk_.arc        2009-07-22 22:00:11 A
/archivelog/2009_07_23/o1_mf_1_113_56gfxz16_.arc        2009-07-23 00:01:03 A
/archivelog/2009_07_23/o1_mf_1_114_56hfrzmd_.arc        2009-07-23 09:04:32 A
/archivelog/2009_07_23/o1_mf_1_115_56hq9grl_.arc        2009-07-23 11:46:55 A
/archivelog/2009_07_23/o1_mf_1_116_56j8shjn_.arc        2009-07-23 16:45:36 A
/archivelog/2009_07_23/o1_mf_1_117_56jv7yb3_.arc        2009-07-23 22:00:31 A
/archivelog/2009_07_24/o1_mf_1_118_56kc5f8m_.arc        2009-07-24 02:32:13 A
/archivelog/2009_07_24/o1_mf_1_119_56lcjpo1_.arc        2009-07-24 11:44:23 A
/archivelog/2009_07_24/o1_mf_1_120_56lhsf9m_.arc        2009-07-24 12:57:17 A
/archivelog/2009_07_24/o1_mf_1_121_56lhvhxq_.arc        2009-07-24 12:58:24 A
/archivelog/2009_07_24/o1_mf_1_122_56lhxg5f_.arc        2009-07-24 12:59:26 A
/archivelog/2009_07_24/o1_mf_1_123_56lhz868_.arc        2009-07-24 13:00:24 A
/archivelog/2009_07_24/o1_mf_1_124_56lj12sz_.arc        2009-07-24 13:01:22 A
/archivelog/2009_07_24/o1_mf_1_125_56lj1vs4_.arc        2009-07-24 13:01:47 A
/archivelog/2009_07_24/o1_mf_1_126_56lj4tmr_.arc        2009-07-24 13:03:22 A
/archivelog/2009_07_24/o1_mf_1_127_56m1vos0_.arc        2009-07-24 18:05:42 A
/archivelog/2009_07_24/o1_mf_1_128_56mhnd1n_.arc        2009-07-24 22:00:44 A
/archivelog/2009_07_25/o1_mf_1_129_56n4qy8n_.arc        2009-07-25 04:01:02 A
/archivelog/2009_07_25/o1_mf_1_130_56nlyzvf_.arc        2009-07-25 08:03:44 A
/archivelog/2009_07_25/o1_mf_1_131_56od9156_.arc        2009-07-25 15:15:45 A
/archivelog/2009_07_25/o1_mf_1_132_56p7pv0x_.arc        2009-07-25 23:03:55 A
/archivelog/2009_07_26/o1_mf_1_133_56q6v4mp_.arc        2009-07-26 07:55:17 A
/archivelog/2009_07_26/o1_mf_1_134_56qzr5bn_.arc        2009-07-26 15:00:22 A
/archivelog/2009_07_26/o1_mf_1_135_56rw3jw9_.arc        2009-07-26 23:04:17 A
/archivelog/2009_07_27/o1_mf_1_136_56srb8h5_.arc        2009-07-27 07:05:45 A
                                                        2009-07-27 15:00:18 D
/archivelog/2009_07_27/o1_mf_1_138_56vdqh0f_.arc        2009-07-27 22:00:15 A
/archivelog/2009_07_28/o1_mf_1_139_56vov462_.arc        2009-07-28 00:35:48 A
/archivelog/2009_07_28/o1_mf_1_140_56wonlh8_.arc        2009-07-28 09:38:27 A
                                                        2009-07-28 09:40:29 D
/archivelog/2009_07_27/o1_mf_1_137_56tn41rl_.arc        2009-07-28 09:40:43 A
/archivelog/2009_07_28/o1_mf_1_141_56wsj62m_.arc        2009-07-28 10:44:22 A

127 rows selected.

sys@ora10g>

2.分別解釋status引數值的含義
STATUS=A Available，表示歸檔日誌檔案有效
STATUS=U Unavailable，表示歸檔日誌檔案無效
STATUS=D Deleted，表示歸檔日誌已經被刪除
STATUS=X eXpired，表示歸檔日誌被使用者使用作業系統的命令刪除了

OK，到此，對上面列出來的資訊有一點感覺了吧。

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-610655/，如需轉載，請註明出處，否則將追究法律責任。

相關文章