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