Discussions
Categories
- 197.2K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Rename Table (Swap)

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).
Best 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
. That is all.
Thank your for your help.
Answers
-
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.
-
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.
-
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?
ALTER TABLE TABLE_A MOVE TABLESPACE TEST2;
-
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 --
-
I will try it.
-
@JuanM There is a XML_TYPE column so the DBMS_REDEFINITON is not support this.
-
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
-
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:
-
Red Hat Enterprise Linux Server release 6.5 (Santiago) 2.6.32 431.el6.x86_64 (64-bit)
-
Bahram Orujov wrote:Red Hat Enterprise Linux Server release 6.5 (Santiago) 2.6.32 431.el6.x86_64 (64-bit)
man ulimit