Copying tables and indexes to another tablespace

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.