?
Solved

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

Posted on 2016-08-07
2
Medium Priority
?
61 Views
Last Modified: 2016-08-11
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

0
Comment
Question by:enthuguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
Abhimanyu Suri earned 2000 total points
ID: 41746370
Column exists error is not taking time as per the given example, it is create index.

Error is for TEMP tablespace being full, please increase the size of it.

You can check from user_tab_columns/dba_tab_columns/all_tab_columns using a combination of schema,table_name,column_name if the column being added already exists or not and take action accordingly.

For example :

SELECT column_name
    FROM dba_tab_columns
   WHERE owner = 'SCHEMA_NAME'
     AND table_name = 'TABLE_NAME'
     AND column_name = 'COLUMN_NAME' --> Name of column being added

Even, oracle itself does the same check along with many others with exception handling.
Thats how it knows that column already exist

Excerpt from 10046 trace :

select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 512779 and bitand(FLAGS, 128)=0
....
....
 execute immediate m_stmt into m_o_stmt;
  if (m_owner!='MDSYS' and m_owner!='SYS' and m_type='TABLE' and m_event='ALTER')
  then
    m_stmt:='select column_name from dba_tab_columns where owner=:1 and table_name=:2';
    open m_cur for m_stmt using m_owner,m_name;
    loop
      fetch m_cur into m_column;
      exit when m_cur%NOTFOUND;
      m_stmt:='select sdo_geor_def.isDropColumn(:1) from dual';
1
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41753001
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.
0

Featured Post

RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Suggested Courses
Course of the Month9 days, 13 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question