-
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.
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|