Re: Block copy a table to another DB

Hi Rich,

You mentioned that:
> the table's blocks span nearly all of the tablespace's datafiles, and
that tablespace contains nearly all of the non-system data, and
> Scenarios like that are what I was (unsuccessfully) trying to imply would
be more difficult and/or time-consuming than a full DB restore

If I understand correctly, you'd like to restore only datafiles belonging to tablespaces which contain the tables with purged LOBs. Any you'd like to avoid restoring the full database. Have you considered restoring only the desired tablespaces/datafiles (along with SYSTEM, UNDO and SYSAUX)? Examples of such scenarios:
https://iarsov.com/oracle/performing-partial-restore-and-recovery/ https://www.pythian.com/blog/how-to-recover-a-subset-of-an-oracle-database/

or a similar functionality, rman duplicate database with SKIP TABLESPACE : http://docs.oracle.com/database/121/BRADV/rcmdupad.htm#GUID-0C6616AE-FF8A-41E1-B0DE-EED457E020FC

That way you avoid putting the source tablespaces in read-only mode as required by TTS, and you also avoid restoring the full database. And since you said that "the table's blocks span nearly all of the tablespace's datafiles", you'd have to have a similar amount of available storage anyway, regardless of which technique you use to "copy" the data. If you have a (physical) standby database you might also consider stopping recovery on it, opening it read-write, perform the testing, flash it back to a time before opening it read-write, and restart redo apply.

If that's a recurring task, you might employ other strategies to preserve storage and speed up cloning, e.g.:
- Delphix
- use of read-write ACFS snapshots (only from 12c on for database files): http://allthingsoracle.com/12c-database-backups-using-acfs-snapshots/ - if you are on multitenant, PDB Snapshot copy: http://blog.dbi-services.com/pdb-snapshot-copy-for-continuous-integration-testing/

Regards,
Jure Bratina

On Thu, Nov 17, 2016 at 4:03 PM, Rich J <rjoralist3_at_society.servebeer.com> wrote:

> On 2016/11/17 08:19, Tefft, Michael J wrote:
>
> Transportable tablespace should do this – but you would need to make the
> tablespace read-only for a bit, or get it from a backup.
>
> Scenarios like that are what I was (unsuccessfully) trying to imply would
> be more difficult and/or time-consuming than a full DB restore.
>
> Thanks!
> Rich
>

--
http://www.freelists.org/webpage/oracle-l