Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Tuesday, 3 October 2017

Add Column in Oracle Table



We can to add new column to existing table by altering table.
 
Syntax:
        Alter table <table_name> add <col  datatype>;
Example:
      ALTER TABLE student ADD phone NUMBER;



You can add more than one column in oracle table by using altering table

 

Syntax:
        Alter table <table_name> add (<col1  datatype>,<col2  datatype>);

Example:
 ALTER TABLE student ADD (phone1 NUMBER, email VARCHAR2(10) );


Share this article :

3 comments:

  1. Hi Dayakar,

    I learnt so much in such little time about Add Column in Oracle Table . Even a toddler could become smart reading of your amazing articles.

    We had requirement that we need to alter table which contains huge data nearly 25 millions records.
    Its range partitioned table and having 60 Partitions .
    We want to add some extra columns to the same table .

    Obviously it will take more time .What is the efficient way of doing this ?
    Do I need to disable all the indexes or Table gathering

    I know that when we have bulk insertions into the table, disabling the indexes will help .
    When we are selecting the data by applying indexed column where conditions , gathering statistics will help .

    But in this case PARALLE HINT will help ?
    or
    Do I need to go the Multi columns alter ?
    or
    Do we have any other best solution?

    Please help me to resolve the problem

    Details : Table Size : 25 Millions Records
    No of Partitions : 60
    Partition Type : Range Partition on EntryDate Column
    Oracle Version :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).

    Gracias
    Irene Hynes

    ReplyDelete
  2. Hi There,

    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on Add Column in Oracle Table !

    I need your help please, I am new at PL/SQL

    I need to run a select command with multiple values , if a break down this query into 3 pieces and run them,they works perfectly, but Ive got problems whenever i execute this query because there are multiple values into the where clause and i think its necessary to create lines break so it may work perfectly

    how can I get through this?

    SELECT job_name, command,description,days_of_week,start_mins,machine, J2.CREATE_STAMP, J2.MODIFY_STAMP
    FROM MDBADMIN.UJO_JOBROW JR JOIN MDBADMIN.ujo_job2 J2
    ON (JR.JOID = J2.JOID)
    WHERE job_name IN ('coscl_PM_zip_log','coscl_PM_clear_log','coscl_PM_alarma_fs','coscl_PM_pmapp1_PM_up','coscl_PM_control_recargas_cod_11','coscl_PM_control_recargas_ingreso','coscl_PM_control_rec_error_cc_agrup_v1'.'coscl_PM_control_procesos_diarios','coscl_PM_rep_recar_x_estado','coscl_PM_control_tiempos_recargas',
    'coscl_PM_control_porcentaje','coscl_PM_control_puertos_bloq','coscl_PM_control_errores', 'coscl_PM_control_java','coscl_PM_control_error_cache');

    Thank you very much and will look for more postings from you.

    Merci,
    Preethi.

    ReplyDelete
  3. The information you have posted is very useful. The sites you have referred was good. Thanks for sharing this: https://view.ly/v/MXfpB9r8EUb2

    ReplyDelete