探索Oracle之数据库升级八12cDowngrade11gR2(十一)
bj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
22 SELECT NULL INTO p_null FROM DBA_OBJECTS
23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
24 object_type = 'TABLE' and rownum <=1;
25
26 -- Select indices to be re-enabled
27 EXECUTE IMMEDIATE q'+
28 SELECT 'ALTER INDEX ' ||
29 local_enquote_name(e.schemaname) || '.' ||
30 local_enquote_name(e.indexname) || ' ENABLE'
31 FROM enabled$indexes e, ind$ i
32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
33 bitand(i.property, 16) != 0+'
34 BULK COLLECT INTO commands;
35
36 IF (commands.count() > 0) THEN
37 FOR i IN 1 .. commands.count() LOOP
38 BEGIN
39 EXECUTE IMMEDIATE commands(i);
40 EXCEPTION
41 WHEN NON_EXISTENT_INDEX THEN NULL;
42 WHEN RECYCLE_BIN_OBJS THEN NULL;
43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
44 END;
45 END LOOP;
46 END IF;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
51 IF rebuild_idx_msg THEN
52 BEGIN
53 DBMS_OUTPUT.PUT_LINE
54 ('The following indexes could not be re-enabled and may need to be rebuilt:');
55
56 OPEN reg_cursor FOR
57 'SELECT e.schemaname, e.indexname
58 FROM enabled$indexes e, ind$ i
59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
60
61 LOOP
62 FETCH reg_cursor INTO p_schemaname, p_indexname;
63 EXIT WHEN reg_cursor%NOTFOUND;
64 DBMS_OUTPUT.PUT_LINE
65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
66 END LOOP;
67 CLOSE reg_cursor;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
72 WHEN OTHERS THEN CLOSE reg_cursor; raise;
73 END;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP function local_enquote_name;
Function dropped.
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components;
...Database user "SYS", database schema "APEX_030200", user# "77" 18:09:01
...Compiled 0 out of 2210 objects considered, 0 failed compilation 18:09:01
...189 packages
...181 package bodies
...360 tables
...12 functions
...19 procedures
...4 sequences
...366 triggers
...946 indexes
...125 views
...0 libraries
...4 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 18:09:01
...Completed key object existence check 18:09:01
...Setting DBMS Registry 18:09:01
...Setting DBMS Registry Complete 18:09:01
...Exiting validate 18:09:01
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
##执行完以上脚本后再做检查，就没有无效对象了
SQL> set line 300
SQL> set line 300
SQL> r
1* SELECT comp_name, status, substr(version,1,10) as version from dba_server_registry order by modified
COMP_NAME STATUS VERSION
----------------------------------- -------------------------------------------- ----------------------------------------
Oracle Workspace Manager VALID 11.