Copying tablespace

[SpiritOfLennon]

I've written a procedure that drops all the tables in a tablespace and copies across tables from another tablespace into the cleared tablespace. Does anybody know of a neat way to copy across the indexes as well?

SOL
I'm only guessing but my guess work generally works for me.

 

[carp]

Try something like the following:

set pagesize 0
set linesize 100
spool index_mover.txt
SELECT 'ALTER INDEX '||index_name||' REBUILD TABLESPACE desired_tablespace_name;' 
FROM dba_indexes
WHERE tablespace_name = 'CURRENT_TABLESPACE_NAME';
spool off
set pagesize 24
set linesize 80

This will generate a script for you; you will probably want to go into it and remove the select statement at the top, then run it from a SQL*Plus script.

Note that this is the bare-bones version; I'm sure SantaMufasa will soon provide us with one of his beautiful scripts that does this and more!

 

[SantaMufasa]

Carp,

Your scripts have always been things of beauty to me. There is no need for me to attempt to "gild the lily", but there is an automated method to remove the SELECT statement ("SET ECHO OFF") and to speed up the INDEX REBUILD (using "PARALLEL") with the following enhancement to Carp's code:

set echo off
set feedback off
accept old_tsname prompt "Enter the name of the old (source) tablespace: "
accept new_tsname prompt "Enter the name of the new (target) tablespace: "
set pagesize 0
set verify off
set trimspool on
spool TempMove&old_tsname..sql
prompt Set echo on
prompt set feedback off
select	'alter index '||owner||'.'||segment_name||' /* size: '||sum(bytes)||
	' */ rebuild parallel tablespace &new_tsname;'
from dba_extents
where tablespace_name = upper('&old_tsname') and segment_type = 'INDEX'
group by owner,segment_name
order by owner, sum(bytes) desc, segment_name
/
prompt set feedback on
spool off
prompt
prompt Wrote 'TempMove&old_tsname..sql'
prompt
set pagesize 35

...but what do I know anyway?

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:42 (28Jul04) UTC (aka "GMT" and "Zulu"), 09:42 (28Jul04) Mountain Time)

 

[carp]

Plenty!~
As evidenced by the greatly enhanced script you've provided!

 

[SpiritOfLennon]

Hi,
Thanks for those maybe I've mistated my requirements, and should have referred to schemas rather than tablespaces. I want to create a new index not recreate an index in a different tablespace.
e.g.
I have a table TABLE1 in schema SCHEMA1 with an index INDEX1
I want to duplicate table TABLE1 in SCHEMA2 with an index INDEX1 where the contents and structure of TABLE1 have been copied across as have the indexes, TABLE1 and INDEX should remain in SCHEMA1. Is this what you're scripts do? I tried alter index and it just seemed to move the index acros to another tablespace.

SOL
I'm only guessing but my guess work generally works for me.

 

[SantaMufasa]

SoL,

Correct, my scripts (as I inferred you wanted from your original specifications) move tables and indexes from one tablespace to another, while retaining the same schema ownership. If you wish to copy tables and indexes from one schema to another, then the best method is via "exp" and "imp" using the "imp" parameters "fromuser=<source_schema>" and "touser=<target_schema>".

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:17 (02Aug04) UTC (aka "GMT" and "Zulu"), 15:17 (02Aug04) Mountain Time)

 

[SpiritOfLennon]

Mufasa,
Thanks for that. I'm aware of the imp and exp commands and do use them, however it takes a long time to do and I was trying to find a faster way. I was doing it more as an exercise in "can it be done another way" than any real requirement, although if disk space if at a premium then imp and exp aren't really ideal methods. I think I've pretty much mastered an automatic script now that drops existing tables, copies across tables from another schema, creates the indexes on the new tables and then estimates the statistics so that it's ready for use. Initial tests seem to indicate that it may work faster.
Thanks for the advice.


SOL
I'm only guessing but my guess work generally works for me.