Mega Code Archive
Validate all objects in a schema
<![CDATA[
SQL>
SQL> CREATE OR REPLACE PROCEDURE validate_structure (i_schema IN VARCHAR2) IS
2 v_sql VARCHAR2 (2000);
3 CURSOR cur_objects (p_schema VARCHAR2) IS
4 SELECT object_name, object_type FROM sys.dba_objects
5 WHERE owner = p_schema AND object_type IN ('CLUSTER', 'TABLE')
6 ORDER BY object_name;
7 BEGIN
8 FOR co IN cur_objects (i_schema)
9 LOOP
10 v_sql := 'ANALYZE ' ||
11 co.object_type ||
12 ' ' ||
13 i_schema ||
14 '.' ||
15 co.object_name ||
16 ' VALIDATE STRUCTURE CASCADE';
17 DBMS_UTILITY.exec_ddl_statement (v_sql);
18 DBMS_OUTPUT.put_line (v_sql);
19 END LOOP;
20 END;
21
22
23 --
]]>