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