DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: script to check integrity constraint, etc.

  1. #1
    Join Date
    Oct 2000
    Posts
    144

    Unhappy

    Hi everyone,

    I just receive atask on writing the script to check all database objects
    that are valid and compiled and all integrity constraints are enabled. I don't know
    where to start. Please help me.





  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi Trina,
    The following script will compile all ur database objects ie.. PROCEDURE,PAckage,package body,function,trigger

    Enter the follwing select command to get inf about all the database obejcts which are invalid
    select owner,object_name,object_type,status from
    dba_objects where status='INVALID';


    spool c:\x\allcompile.sql
    select 'ALTER '||object_type ||' '||owner||'.'||object_name||' '|| 'COMPILE;'
    from dba_objects where status='INVALID';
    spool off

    After running the above script ,run the allcompile.sql script to compile all the invalid objects.


    Now regarding ur constraints,u can check it from dba_constraints.
    U can fire the following query to get all the information about the constraint

    select a.owner,b.column_name,b.constraint_name,decode(a.constraint_type,'C','Check','P','Primary',
    'U','Unique','R','Referential')"Constraint Type",a.search_condition
    from dba_constraints a,dba_cons_columns b
    where a.table_name=upper('&table') and a.owner='CIIM86' and
    a.constraint_name=b.constraint_name;


    In case of any help needed please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  3. #3
    Join Date
    May 2001
    Location
    Chantilly, Virginia
    Posts
    30
    Hi,

    To find the status of all objects use the following query:

    select object_name, object_type, status from all_objects
    where owner = 'Your_Owner'
    order by 1,2;

    To check the status of constraints, you can use the query:

    select constraint_name, constraint_type, status
    from all_constraints
    where owner = 'Your_owner'
    order by constraint_name

    To get the names of all Primary & foreign key constraints existing, you can use:(provided the constraint names are prefixed/suffixed by PK/FK)

    select table_name|| ' | ' || column_name|| ' | ' || constraint_name from all_cons_columns
    where owner = 'Your_Owner'
    and constraint_name like '%K%'
    order by table_name


    Hope this will help you to get started.

    Good Luck
    Prasad.



  4. #4
    Join Date
    Oct 2000
    Posts
    144
    Thanks Rohit and Sairaj.

    For Rohit, I just test your script and gives me the error
    below. I'm not sure what the errors are. Also, I'm not
    familiar with the word COMPILE. Would you kindly
    explaining to me what the COMPILE does? I really appreciate
    it.


    ALTER PACKAGE BODY MDSYS.MDEXEX COMPILE
    *
    ERROR at line 1:
    ORA-00922: missing or invalid option

    ALTER TYPE MDSYS.SDO_ELEM_INFO_ARRAY COMPILE
    *
    ERROR at line 1:
    ORA-02311: cannot alter with COMPILE option a valid type with type or table
    dependents


    ALTER TYPE MDSYS.SDO_ORDINATE_ARRAY COMPILE
    *
    ERROR at line 1:
    ORA-02311: cannot alter with COMPILE option a valid type with type or table
    dependents

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width