Steps To Be Followed Before Altering A Table

STEPS TO BE FOLLOWED BEFORE ALTERING A TABLE Whenever a table is altered all the corresponding objects (Functions, Procedures, Package, Triggers, Views) will get invalid. Therefore before altering a table we need to note these objects and compile them, after the table is altered. To get a list of objects which corresponds to the table(which needs to be altered) issue the following command: select distinct a.name,a.type,a.referenced_owner,a.referenced_name,b.owner,b.status from dba_dependencies a, dba_objects b where a.name=b.object_name and a.referenced_name='CODE_SHEET' order by a.type; In the above query ‘TMP’ is the table, replace it with the required table. Once the table is altered issue the following query to generate the compiling statements:

select distinct 'Alter ' || a.type || ' '|| a.owner || '.' || a.name || ' compile;' from dba_dependencies a, dba_objects b where a.name=b.object_name and a.referenced_name ='TR_GIN_RECP_DETAIL ' and b.status='INVALID'; Some of the procedures or functions may be created using views, in such cases these procedures or functions will not be listed. Therefore we need to find them individually and compile them. To do so replace the column a.referenced_name in the above queries with the Viewname. Note: 1. Synonymns will not be listed in dba_dependencies. 2. Any object which uses synonyms will be listed. Eg: if a procedure uses the synonym name then the procedure name will be listed in dba_dependencies. 3. Any object which uses Views will not be listed. Eg: if a procedure uses the view name then the procedure name will not be listed in dba_dependencies. THE BELOW QUERY IS USED TO IDENTIFY THE USER WHO ARE USING THE PARTICULAR TABLE

select s.sid, s.serial#, s.username, o.owner,s.osuser,s.terminal,o.object_type, s.status,o.object_name from v$session s,v$lock l, dba_objects o where s.sid=l.sid and o.object_id=l.id1 and s.username is not null and object_name='PERD_TRANS’;