CREATE OR REPLACE PROCEDURE ANALYZE_TB AS
OWNER_NAME VARCHAR2(100);
V_LOG INTEGER;
V_SQL1 VARCHAR2(800);
V_TABLENAME VARCHAR2(50);
CURSOR CUR_LOG
IS
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'ANALYZE_LOG';
--1
BEGIN
--DBMS_OUTPUT.ENABLE (buffer_size=>100000);
--1.1
BEGIN
OPEN CUR_LOG;
FETCH CUR_LOG
INTO V_LOG;
IF V_LOG = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
END IF;
END;
SELECT USER INTO OWNER_NAME FROM DUAL;
V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
OWNER_NAME || ''',''ANALYZE BEGIN'',''ALL'')'; EXECUTE IMMEDIATE V_SQL1; sys.dbms_stats.gather_schema_stats(ownname => UPPER(OWNER_NAME), estimate_percent => 100, method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);
V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
OWNER_NAME || ''',''ANALYZE END'',''ALL'')'; EXECUTE IMMEDIATE V_SQL1; commit;
--1.2 delete tmptbstatitics and lock statistics BEGIN for x in (select a.table_name, a.last_analyzed, b.stattype_locked from user_tables a, user_tab_statistics b where a.temporary = 'Y' and a.table_name = b.table_name and (b.STATTYPE_LOCKED is null or
a.last_analyzed is not null)) LOOP
IF x.last_analyzed IS NOT NULL THEN
--delete stats dbms_stats.delete_table_stats(ownname => user, tabname =>x.table_name,
force => TRUE);
END IF;
IF x.stattype_locked
IS
NULL THEN
--lock stats dbms_stats.lock_table_stats(ownname => user, tabname =>x.table_name);
END IF; END LOOP; end;
EXCEPTION
WHEN OTHERS THEN
IF CUR_LOG%ISOPEN THEN
CLOSE CUR_LOG; END IF; commit; end;
/
SQL> show errors;
PROCEDURE ANALYZE_TB 出现错误:
LINE/COL ERROR
27/17 PLS-00103: 出现符号 "IS"在需要下列之一时：
:= . ( @ % ;
27/31 PLS-00103: 出现符号 "LOOP"在需要下列之一时：
. ( ) , * % & = - + < / >
at in is mod remainder not rem
<> or != or ~= >= <= <> and or like like2 like4 likec between
|| multiset member submultiset
看看是不是符号的问题
https://blog.csdn.net/liuxingbangly/article/details/24693959