Forum Stats

  • 3,728,705 Users
  • 2,245,678 Discussions
  • 7,853,707 Comments

Discussions

Rename Table (Swap)

Bahram Orujov
Bahram Orujov Member Posts: 42 Red Ribbon

Hi. I have two tablespacece and two table as you see. Table A is in test1 tablespace and table B is in test2 tablespace. I created table B using:

create table b as select * from A;

After that I want to rename Table B to Table A. But I don't want to lost my indexes, treiggers and all kind kind of object of old Table A.

I want to inherit all "objects" of Table A(old table A) to Table B (new table b).

Untitled.png

Tagged:
Marcus RangelBahram Orujov

Best Answer

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018 Accepted Answer

    I found new way to solve this. So I created a copy of table  A as Table B in the tablespace 2 (create table b tablespace test2 as select * from a). After that I truncated Table A and moved it to tablespace test2. And I insert data back from Table B to Table A. (insert into A select * from B). That is all.

    Thank your for your help.

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited February 2018
    Bahram Orujov wrote:Hi. I have two tablespacece and two table as you see. Table A is in test1 tablespace and table B is in test2 tablespace. I created table B using: create table b as select * from A; After that I want to rename Table B to Table A. But I don't want to lost my indexes, treiggers and all kind kind of object of old Table A.I want to inherit all "objects" of Table A(old table A) to Table B (new table b).
    Untitled.png

    Some, many, most professional IT groups maintain a source code repository for application source code that includes all DDL to create application objects.

    You could extract desired DDL from your code repository.

    Alternatively you could use  DBMS_METADATA to extract desired DDL from the data dictionary.

  • JuanM
    JuanM Member Posts: 2,144 Gold Trophy
    edited February 2018

    if you are using oracle EE, then use online table redefinition

    https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#ADMIN01514

    or create manually the dependent objects on table B.

  • Marcus Rangel
    Marcus Rangel Member Posts: 388 Bronze Badge
    edited February 2018

    I'm not sure if I understand your requirements, but can't you just rename the table and then move it to the TEST2 tablespace? Or maybe just move it?

    https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/RENAME.html#GUID-573347CE-3EB8-42E5-B4D5-EF71CA06FAFC

    ALTER TABLE TABLE_A MOVE TABLESPACE TEST2;

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited February 2018

    Bahram, if you do not need to keep the original tableA and want to relocate it to a new tablespace then I think Marcus provided you the best solution to just use ALTER TABLE x MOVE TABLESPACE y to relocate the table.  You will then need to use ALTER INDEX x REBUID to revalidate the indexes.  This should work as long as TableA does not have any LONG or LONG RAW columns.  There is no loss of grants, constraints, indexes, triggers, etc...

    - -

    Otherwise John pointed out you can use DBMS_METADATA to generate the DDL to build the indexes, constraints, triggers, etc...  Extracting the grants as SQL is very easy to do just by querying the RDBMS dictionary views such as DBA_TAB_PRIVS.

    - -

    Both of the above approaches will require an outage where the table is not available for use.  If you need to keep the table available then you can look into how to use the DBMS_REDEFINITION package to recreate the table as JuanM pointed out.

    - -

    HTH -- Mark D Powell --

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018

    I  will try it.

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018

    @JuanM There is a XML_TYPE column so the DBMS_REDEFINITON is not support this.

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018

    I move all my table using this.

    'ALTER TABLE TABLE MOVE TABLESPACE TEST2;

    But when I try this for table a, after a few seconds there are ORA:4030 error

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited February 2018
    Bahram Orujov wrote:I move all my table using this.'ALTER TABLE TABLE MOVE TABLESPACE TEST2;But when I try this for table a, after a few seconds there are ORA:4030 error

    What is OS name & version?

    [[email protected] ~]$ oerr ora 4030

    04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"

    // *Cause:  Operating system process private memory was exhausted.

    // *Action:

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018

    Red Hat Enterprise Linux Server release 6.5 (Santiago) 2.6.32 431.el6.x86_64 (64-bit)

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited February 2018
    Bahram Orujov wrote:Red Hat Enterprise Linux Server release 6.5 (Santiago) 2.6.32 431.el6.x86_64 (64-bit)

    man ulimit

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    edited February 2018

    I created table B using:

    create table b as select * from A;

        {some time will pass, maybe short, maybe long}

    After that I want to rename Table B to Table A.

    What happens in that time between your operations if someone changes data in table A? Your changes won't be reflected in Table B.

    Cheers,
    Brian

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018

    There is not more 10 DML every day. I recover it manually after rename table.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited February 2018

    Bahram, why would you need to do this every day?  This kind of request is usually a one time deal.  What business function/process underlies this task?

    - -

    HTH -- Mark D Powell --

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018

    Not every day I do this process.

  • jgarry
    jgarry Member Posts: 13,842
    edited February 2018

    Not entirely sure what you are trying to accomplish and how often, but perhaps having two tables and switching a synonym between them will allow periodic changes with the db manipulation separate from normal usage.  This might have unfortunate plan change effects if one is local and the other remote.

  • Unknown
    edited February 2018
    After that I want to rename Table B to Table A. But I don't want to lost my indexes, treiggers and all kind kind of object of old Table A.

    1. You can't rename B to A is A still exists.

    2. Table B does NOT have any indexes - renaming it will NOT add any indexes

    3. If you drop table A the indexes will also be dropped

    I want to inherit all "objects" of Table A(old table A) to Table B (new table b).

    There is NO SUCH THING - as inheriting 'all objects' of a table.

    Why do you have two tables with the same data?

  • Bahram Orujov
    Bahram Orujov Member Posts: 42 Red Ribbon
    edited February 2018 Accepted Answer

    I found new way to solve this. So I created a copy of table  A as Table B in the tablespace 2 (create table b tablespace test2 as select * from a). After that I truncated Table A and moved it to tablespace test2. And I insert data back from Table B to Table A. (insert into A select * from B). That is all.

    Thank your for your help.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited February 2018

    so mark this thread as ANSWERED

    Marcus RangelBahram Orujov
  • Unknown
    edited February 2018
    I found new way to solve this. So I created a copy of table A as Table B in the tablespace 2 (create table b tablespace test2 as select * from a). After that I truncated Table A and moved it to tablespace test2. And I insert data back from Table B to Table A. (insert into A select * from B). That is all.

    If you just wanted the table moved you should have just 'solved' the original memory problem.

    And if you have partitioning you could likely use EXCHANGE PARTITION to swap the segments rather than another insert.

    Marcus Rangel
This discussion has been closed.