Q
Problem solve Get help with specific problems with your technologies, process and projects.

Copying tables and indexes to another tablespace

I've just read your answer on how to move tables and indexes to another tablespace. I would like to do this as a cloning process so that the tables and indexes stay put in the source but are copied/moved into a target database without having to do a long import.

Hi Brian, I've just read your answer on how to move tables and indexes to another tablespace. I would like to do this as a cloning process so that the tables and indexes stay put in the source but are copied/moved into a target database without having to do a long import.
Oracle does not let you have two copies of the same object all owned by the same user. However, you can create a new user and clone the objects in to the new user. Assume we have a user SCOTT. We can create a new user in a new tablespace:
CREATE TABLESPACE new_ts;
CREATE USER scott_copy IDENTIFIED BY password
DEFAULT TABLESPACE new_ts;
Now that we have the user and tablespace set up, let's copy SCOTT's tables to the new user and new tablespace:
SPOOL copy_tables.sql
SELECT 'CREATE TABLE scott_copy.'||table_name||' AS '||
   'SELECT * FROM scott.'||table_name||';'
FROM dba_tables WHERE owner='SCOTT';
SPOOL OFF
@copy_tables.sql
Now that the tables have been "cloned," let's clone the indexes as well:
SPOOL copy_indexes.sql
SELECT dbms_metadata.get_ddl('INDEX',owner,index_name)
FROM dba_indexes WHERE owner='SCOTT';
SPOOL OFF
{ Modify the spool file to change the index owner to SCOTT_COPY }
@copy_indexes.sql
Once you have the indexes and their tables in the new tablespace, you can use Transportable Tablespaces to copy the data to a new database.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close