8 Replies Latest reply: Dec 4, 2012 4:15 AM by user522961 RSS

    alter table

    user522961
      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
          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
            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
              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
                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
                  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
                    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
                      thank.
                      For the moment I do not need to keep indexes or constreints in user1.SP_TABLE.
                      • 8. Re: alter table
                        user522961
                        Thanks.