Rename Table (Swap)

Bahram Orujov

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).

Bahram Orujov

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 . That is all.

Thank your for your help.

John Thorton

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).

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

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

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

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

I  will try it.

Bahram Orujov

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

Bahram Orujov

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

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

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

John Thorton

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

man ulimit

BPeaslandDBA

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

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

Mark D Powell

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

Not every day I do this process.

jgarry

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

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

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 . That is all.

Thank your for your help.

John Thorton

so mark this thread as ANSWERED

Unknown

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 . 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.