|
bms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2013-09-04 13:26:34
SQL>
SQL> Rem
SQL> Rem Re-enable functional indexes disabled by the recompile
SQL> Rem
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 table_exists number;
5 BEGIN
6 -- Check for existence of the table marking disabled functional indices
7 SELECT count(*) INTO table_exists FROM DBA_OBJECTS
8 WHERE owner = 'SYS' and object_name = 'UTLIRP_ENABLED_FUNC_INDEXES' and
9 object_type = 'TABLE';
10
11 IF (table_exists > 0) THEN
12 -- Select indices to be re-enabled
13 EXECUTE IMMEDIATE q'+
14 SELECT 'ALTER INDEX "' || u.name || '"."' || o.name || '" ENABLE'
15 FROM utlirp_enabled_func_indexes e, ind$ i, obj$ o, user$ u
16 WHERE e.obj# = i.obj# AND i.obj# = o.obj# and o.owner# = u.user#
17 AND bitand(i.flags, 1024) != 0+'
18 BULK COLLECT INTO commands;
19
20 IF (commands.count() > 0) THEN
21 FOR i IN 1 .. commands.count() LOOP
22 EXECUTE IMMEDIATE commands(i);
23 END LOOP;
24 END IF;
25
26 EXECUTE IMMEDIATE 'DROP TABLE utlirp_enabled_func_indexes';
27 END IF;
28 END;
29 /
PL/SQL procedure successfully completed.
SQL>
SQL> DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;
OBJECTS WITH ERRORS
-------------------
7
SQL>
SQL>
SQL> DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> SQL> SQL>
SQL>
SQL>
13,database migration finished so far
14, check the related tablespaces whether it is in the database or not
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------
MMDB_LOB1
MMDB_NDX1
MMDB_DAT1
PMDB_LOB1
PMDB_NDX1
PMDB_DAT1
USERS
SYSAUX
UNDO
SYSTEM
10 rows selected.
SQL |