This discussion is archived
8 Replies Latest reply: Dec 4, 2012 2:15 AM by user522961 RSS

alter table

user522961 Newbie
Currently Being Moderated
Hi all,
I have the same table for two users with differnt number of columns :
 select count(*),owner from all_tab_columns where table_name='SP_TABLE'' group by owner;

  COUNT(*) OWNER
---------- ------------------------------
        65 USER1
       130 SYSADM
Is there any easy way to alter table for USER1 so to have the same columns as for SYSADM

Thanks.
  • 1. Re: alter table
    908002 Expert
    Currently Being Moderated
    create table temp as select * from user1.sp_table;

    drop table user1.sp_table;

    create table user1.sp_table as (select * from sysdam.sp_table where 1=2);

    insert into user1.sp_table (column list....) values ( select col1,c ol2... from temp);
  • 2. Re: alter table
    Lukasz Mastalerz Expert
    Currently Being Moderated
    If you want all new columns at the end of the table (you have to adjust query to support all datatypes appropriately):
    SQL> CREATE TABLE t1 (c1 NUMBER);
    
    Table created.
    
    SQL> CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER);
    
    Table created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  SELECT 'ALTER TABLE c1 ADD COLUMN ' || column_name || ' ' || data_type || ';'
      2  FROM   user_tab_columns
      3  WHERE  table_name = 'T2'
      4  AND    column_name NOT IN (SELECT column_name
      5                             FROM   user_tab_columns
      6                             WHERE table_name = 'T1')
      7* ORDER BY column_id
    SQL> /
    
    'ALTERTABLEC1ADDCOLUMN'||COLUMN_NAME||''||DATA_TYPE||';'
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ALTER TABLE c1 ADD COLUMN C2 NUMBER;
    ALTER TABLE c1 ADD COLUMN C3 NUMBER;
    
    SQL>
    Lukasz
  • 3. Re: alter table
    user522961 Newbie
    Currently Being Moderated
    thank.
    Sorry. I forgot to mentionne that they have not exactly the same data. Then I can not drop the USER1.SP_TABLE.
  • 4. Re: alter table
    BluShadow Guru Moderator
    Currently Being Moderated
    user522961 wrote:
    thank.
    Sorry. I forgot to mentionne that they have not exactly the same data. Then I can not drop the USER1.SP_TABLE.
    Kiran wasn't losing the data. The data was copied into a temporary table then the table was dropped (which could have been done quicker using a rename of the table), and then a new version of the table created based on the other version of it from the other schema, and then the data was copied back in from the temporary table to the correct places in the new table (and then you'd want to drop the temporary table when you're happy with it all).

    Edit: forgot to add...

    There may be an issue with doing it that way if there are key constraints and/or indexes on the table that also need to be taken account of, however, you've not provided full details, so we cannot really comment.

    Edited by: BluShadow on 29-Nov-2012 11:18
  • 5. Re: alter table
    user522961 Newbie
    Currently Being Moderated
    thanks to all.

    What more details do you need ?

    As I have about 60 new columns how to avoid :
    CREATE TABLE t2 (c1 NUMBER, c2 NUMBER, c3 NUMBER ........................ c164 TYPE);
  • 6. Re: alter table
    BluShadow Guru Moderator
    Currently Being Moderated
    Well, for a full answer you'd have to provide full details of the table structures, including any indexes, contraints and potentially any synonyms etc.
  • 7. Re: alter table
    user522961 Newbie
    Currently Being Moderated
    thank.
    For the moment I do not need to keep indexes or constreints in user1.SP_TABLE.
  • 8. Re: alter table
    user522961 Newbie
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points