Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Can anyone please tell me what's going on here:

 cols ora_name_list_t; 
 IF (ora_dict_obj_type = 'TABLE') THEN 
  select column_name bulk collect into cols from user_tab_cols where table_name =   ora_dict_obj_name; 
  FOR i IN cols.first .. cols.last LOOP 

The above trigger is expected to display the list of all the columns in a table after the ALTER TABLE command is executed.

The following works just fine when I ALTER a table with column(s) without any constraints:

Alter Table tab_xxx ADD(nucol char);

However, if I add a new column(s) with a constraint like

ALTER TALE tab_xxx ADD(n number NOT NULL); 

then this new column(s) is not immediately reflected in the USER_TAB_COLS DD view and the FOR loop above to display the names of all the columns DOESN'T show this NEW column added.

Thanks already.

share|improve this question
It looks like the trigger fires while the DDL is not completely finished. This seems to happen if you add any constraint with a column. I have no idea why. – Vincent Malgrat Nov 26 '13 at 16:04
Exactly. And to add to it. If the column has a constraint(any) it seems the DD views(user_tab_cols, user_constraints) is not updated immediately. And, if the constraint is either a Primary Or Unique which results in the creation of an Index, the DD view User_indexes gets updated immediately with the entry of this new index for the PK or Unique key, however, if you go check the user_ind_columns for the columns on which the index has been created... the info is not available there. – OraNewUser Nov 27 '13 at 9:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.