Link to home
Start Free TrialLog in
Avatar of enthuguy
enthuguyFlag for Australia

asked on

Best way to rerun DDL in oracle database within shell script on linux

Hi,
we have a set of DDL which I execute every time as part of a process on oracle database.

1. First time when we execute, it creates and alter tables.
2. On all the subsequent executions it will throw an error which I ignore safely. Since they already exist in the database.
3. I agree this is not the best way to re-execute. I should have some kind of check before re-executing each ddl.
4. Recently it is taking time between 20 mins to 50 mins to even throw error depends on the environments

Pls help what is the best way to check if column/index already exist and skip this.

thanks in advance

Below are the sample script with only few ddl and their error msg and elapsed time.

alter table SAMPLE.EIM_GROUP
  add "REL_SEQ_FLG" VARCHAR2(1 CHAR); 

  ERROR at line 2:
ORA-01430: column being added already exists in table


Elapsed: 00:00:00.01

alter table SAMPLE.EIM_GROUP
  add "SCORE" NUMBER(10,0);
ERROR at line 2:
ORA-01430: column being added already exists in table


Elapsed: 00:00:00.01

CREATE INDEX SAMPLE.CHILD_X1_X ON SAMPLE.CON_CHILD (ATTRIB_07,UCM_TYPE_CD,OBJECT_TYPE,OBJECT_TABLE,CREATED) TABLESPACE USER_TBLSPACE;
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP


Elapsed: 00:07:21.57

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Abhimanyu Suri
Abhimanyu Suri
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The best way to handle this is likely in a PL\SQL procedure (or a group of procedures)  That would allow you to execute a query first, then depending on whether the query determines the object already exists or not, the PL\SQL procedure can decide to execute the DDL command or not.  

Some of the data dictionary views that would be the most helpful to write your queries to select from are:
dba_tables
dba_tab_columns
dba_indexes

If you don't have access to these "dba_..." views, use the corresponding "all_..." views instead.

And, be aware that in PL\SQL you can't execute DDL commands directly like you can in a *.sql script.  In PL\SQL, these can only be executed inside the "execute immediate" command or in procedures from the supplied DBMS_SQL package.