IT Knowledge oracle

1. Expdp/impdp

​

1.1. expdp/impdp

1.2 Validation by DBA

​​

Compile invalid object. 

​

BEGIN

    FOR rec IN (SELECT object_type, owner, object_name

                FROM dba_objects

                WHERE status = 'INVALID')

    LOOP

        BEGIN

            IF rec.object_type = 'PACKAGE' THEN

                EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            ELSIF rec.object_type = 'PACKAGE BODY' THEN

                EXECUTE IMMEDIATE 'ALTER PACKAGE ' || rec.owner || '.' || rec.object_name || ' COMPILE BODY';

            ELSIF rec.object_type = 'PROCEDURE' THEN

                EXECUTE IMMEDIATE 'ALTER PROCEDURE ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            ELSIF rec.object_type = 'FUNCTION' THEN

                EXECUTE IMMEDIATE 'ALTER FUNCTION ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            ELSIF rec.object_type = 'TRIGGER' THEN

                EXECUTE IMMEDIATE 'ALTER TRIGGER ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            ELSIF rec.object_type = 'VIEW' THEN

                EXECUTE IMMEDIATE 'ALTER VIEW ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            ELSIF rec.object_type = 'TYPE' THEN

                EXECUTE IMMEDIATE 'ALTER TYPE ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            ELSIF rec.object_type = 'TYPE BODY' THEN

                EXECUTE IMMEDIATE 'ALTER TYPE ' || rec.owner || '.' || rec.object_name || ' COMPILE BODY';

            ELSIF rec.object_type = 'SYNONYM' THEN       

                 EXECUTE IMMEDIATE 'ALTER SYNONYM ' || rec.owner || '.' || rec.object_name || ' COMPILE';

            END IF;

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('Error compiling ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ': ' || SQLERRM);

        END;

    END LOOP;

END;

/

​

spool dbname.log append

connect username@db_name

select user,global_name  from dual,global_name;

@oracle/sql/validate_db_admin.sql

​

​

​