oracle v$sql_plan周期,DBA_HIST_SQL_PLAN保留多长时间的数据?

今天又在一个生产库上干了一个表的一堆索引(这个表原先有37个索引)。后来还是发现有个索引不该干，补了一个回去。不过，怎么说索引多了看不下去，干》不干。

查sql使用情况：

WITH Q AS

(SELECT S.OWNER A_OWNER,

TABLE_NAME A_TABLE_NAME,

INDEX_NAME A_INDEX_NAME,

INDEX_TYPE A_INDEX_TYPE,

SUM(S.bytes) / 1048576 A_MB

FROM DBA_SEGMENTS S, DBA_INDEXES I

WHERE S.OWNER = UPPER('&INPUT_OWNER')

AND I.OWNER = UPPER('&INPUT_OWNER')

AND INDEX_NAME = SEGMENT_NAME

GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE)

SELECT A_OWNER OWNER,

A_TABLE_NAME TABLE_NAME,

A_INDEX_NAME INDEX_NAME,

A_INDEX_TYPE INDEX_TYPE,

A_MB MB,

DECODE(OPTIONS, null, '       -', OPTIONS) INDEX_OPERATION,

COUNT(OPERATION) NR_EXEC,

(WITH TMP AS (SELECT TABLE_NAME,

INDEX_NAME,

TO_CHAR(WM_CONCAT(COLUMN_POSITION)

OVER(PARTITION BY INDEX_NAME ORDER BY

COLUMN_POSITION)) COLUMN_POSITION,

TO_CHAR(WM_CONCAT(COLUMN_NAME)

OVER(PARTITION BY INDEX_NAME ORDER BY

COLUMN_POSITION)) COLUMN_NAME

FROM USER_IND_COLUMNS)

SELECT MAX(COLUMN_NAME) AS COLUMN_NAME

FROM TMP

GROUP BY INDEX_NAME

HAVING MAX(TABLE_NAME) = UPPER('&INPUT_TBNAME')

AND A_INDEX_NAME = INDEX_NAME) COLUMN_NAME,

'DROP INDEX ' || A_INDEX_NAME || ';' SQL_DROP

FROM Q, DBA_HIST_SQL_PLAN d

WHERE D.OBJECT_OWNER(+) = q.A_OWNER

AND D.OBJECT_NAME(+) = q.A_INDEX_NAME

AND Q.A_TABLE_NAME = UPPER('&INPUT_TBNAME')

GROUP BY A_OWNER,

A_TABLE_NAME,

A_INDEX_NAME,

A_INDEX_TYPE,

A_MB,

DECODE(OPTIONS, null, '       -', OPTIONS)

ORDER BY NR_EXEC