Re: How to backup/restore just one schema??

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Wed, 12 May 2004 23:40:40 -0400
Message-ID: <20040513034040.GA3181@medo.noip.com>

If you are running Oracle 10, then the following becomes possible:

$ expdp system directory=tmp dumpfile=scott.tmp logfile=scott.log schemas=\(' >

Export: Release 10.1.0.2.0 - Production on Wednesday, 12 May, 2004 23:36

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=tmp dumpfile=scott.tmp logfile=scott.log schemas=(SCOTT) Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."PLAN_TABLE"                            0 KB       0 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/scott.tmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:36

$
The main difference is speed. You can do expdp in parallel, in contrast to ordinary exp, which doesn't have the parallel argument. That means that my SCOTT schema is well protected and that I will not lose my precious data in it.

On 05/12/2004 11:28:07 PM, Tim Gorman wrote:
> When using "exp" as a backup utility, don't forget to specify CONSISTENT=Y
> as well. Worst time to realize that related tables are referentially
> inconsistent is when you need data restored...
>
>
>
> on 5/12/04 9:08 PM, Sinardy Xing at SinardyXing_at_bkgcomsvc.com wrote:
>
> > use exp help=3Dy
> >
> > tables=3Dabc.tables
> >
> > imp help=3Dy
> >
> > fromuser, touser
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 12 2004 - 22:37:38 CDT