SCRIPT: VALIDATE.SQL to ANALYZE .. VALIDATE STRUCTURE objects in a Tablespace
Execution Environment: <SQL, SQL*Plus, iSQL*Plus> Access Privileges: Requires to be run connected as SYS schema Usage: In SQL*Plus connect SYS AS SYSDBA. In 8i connect internal can be used Instructions:
Steps to install: 1. Install this package in the SYS schema Eg: SQL> @validate This should create the "ValidateStructure" package. Steps to use: 1. Ensure SPOOL is enabled to catch output and enable SERVEROUT Eg: spool myvalidate.log execute dbms_output.enable(1000000); set serveroutput on 2. Run one of: execute ValidateStructure.TS('TABLESPACE_NAME', TRUE); or execute ValidateStructure.TS('TABLESPACE_NAME', FALSE); to check objects in the named tablespace CASCADE or NOT CASCADE or execute ValidateStructure.TS('TABLESPACE_NAME', TRUE|FALSE, TRUE ); to check objects in the named tablespace using the ONLINE option This will run until all requested items are scanned. 3. Errors from the ANALYZE commands are output to DBMS_OUTPUT and so any failing objects are listed when all TABLES / CLUSTERS have been analyzed. More detailed output from failing ANALYZE commands will be written to the user trace file in USER_DUMP_DESTScript:
REM ======================= Start of Script ============================ REM VALIDATE.SQL REM REM Purpose: The purpose of this package is to check all objects REM in a given tablespace using the REM ANALYZE TABLE .. VALIDATE STRUCTURE [CASCADE]; REM command. REM The package finds all TABLES and CLUSTERS in the REM given tablespace and issues the relevant ANALYZE REM commands. REM REM USAGE REM ~~~~~ REM Please note this is an example script only. REM There is no guarantee associated with the output it presents. REM REM Steps to install: REM 1. Install this package in the SYS schema REM Eg: connect internal REM @validate REM This should create the "ValidateStructure" package. REM REM Steps to use: REM 1. Ensure SPOOL is enabled to catch output and enable SERVEROUT REM Eg: REM spool myvalidate.log REM execute dbms_output.enable(1000000); REM set serveroutput on REM REM 2. Run one of: REM execute ValidateStructure.TS('TABLESPACE_NAME', TRUE); REM or REM execute ValidateStructure.TS('TABLESPACE_NAME', FALSE); REM REM to check objects in the named tablespace CASCADE or NOT CASCADE REM REM or. REM execute ValidateStructure.TS('TABLESPACE_NAME', TRUE|FALSE, TRUE ); REM REM to check objects in the named tablespace using the ONLINE option REM REM This will run until all requested items are scanned.REM REM 3. Errors from the ANALYZE commands are output to DBMS_OUTPUT REM and so any failing objects are listed when all TABLES / CLUSTERS REM have been analyzed. More detailed output from failing ANALYZE REM commands will be written to the user trace file in USER_DUMP_DEST REM set serverout on CREATE OR REPLACE PACKAGE ValidateStructure AS procedure ts( name varchar2 , casc boolean default true, oln boolean default false); END; / CREATE OR REPLACE PACKAGE BODY ValidateStructure AS numbad number:=0; -- procedure item( typ varchar2 , schema varchar2, name varchar2, casc boolean default true,part varchar2 default 'NO', oln boolean default false) is stmt varchar2(200); c number; opt varchar2(20):=' '; begin if (casc) then opt:=' CASCADE '; end if; if (oln) then if typ = 'CLUSTER' then opt:=opt || ' '; else opt:=opt || ' ONLINE '; end if; end if; if part = 'YES' then opt:=opt || ' into INVALID_ROWS '; end if; c:=dbms_sql.open_cursor; begin stmt:='ANALYZE '||typ||' "'||schema||'"."'||name||'" '|| 'VALIDATE STRUCTURE'||opt; dbms_sql.parse(c,stmt,dbms_sql.native); exception when others then dbms_output.put_line( 'Error analyzing '||typ||opt||'"'||schema||'.'||name||'" '||sqlerrm); numbad:=numbad+1; end; dbms_sql.close_cursor(c); end; -- procedure ts( name varchar2 , casc boolean default true, oln boolean default false) is cursor c is SELECT 'TABLE' typ,owner, nvl( IOT_NAME, TABLE_NAME) table_name, partitioned FROM DBA_TABLES where tablespace_name=upper(name) UNION ALL SELECT 'CLUSTER',owner, cluster_name, 'NO' FROM DBA_CLUSTERS where tablespace_name=upper(name); n number:=0; begin numbad:=0; for R in C loop n:=n+1; ValidateStructure.item(R.typ,r.owner,r.table_name, casc, r.partitioned, oln); end loop; dbms_output.put_line('Analyzed '||N||' objects with '||numbad||' errors'); if (numbad>0) then raise_application_error(-20002, numbad||' errors - SET SERVEROUT ON to view details'); end if; end; -- BEGIN dbms_output.enable(1000000); END; / REM ======================== End of Script ============================Sample Output:
SQL> spool myvalidate.log SQL> execute dbms_output.enable(1000000); PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> execute ValidateStructure.TS('EXAMPLE',FALSE); Analyzed 34 objects with 0 errors Analyzed 34 objects with 0 errors PL/SQL procedure successfully completed.
posted on 2009-08-25 22:28 Oracle和MySQL 阅读(268) 评论(0) 编辑 收藏 举报