Tuesday 30 August 2016

How to take the backup at schema level?

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH           DALLAS
        30 SALES                   CHICAGO
        40 OPERATIONS       BOSTON
        50 rolling                     hyderabad
        60 ebus                        ananthapur
        70 subbu                      rajahmundry

7 rows selected.

[oracle@appsdb exp]$ exp file=schema.dmp log=schema.log user=abc
LRM-00101: unknown parameter name 'user'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
[oracle@appsdb exp]$ exp file=schema.dmp log=schema.log owner=abc

Export: Release 11.2.0.1.0 - Production on Tue Aug 30 18:46:00 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Username: abc
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ABC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ABC
About to export ABC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ABC's tables via Conventional Path ...
. . exporting table                           DEPT          7 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          DUMMY          1 rows exported
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

here we had exported the data of abc user in schema.dmp
now we are going to import the data from schema.dmp to a new user xyz.

SQL> conn xyz/xyz 
Connected.
SQL> select * from tab;

no rows selected
here there is no data.

[oracle@appsdb exp]$ imp file=schema.dmp log=schema1.log fromuser=abc touser=xyz

Import: Release 11.2.0.1.0 - Production on Tue Aug 30 18:51:00 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by ABC, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing ABC's objects into XYZ
. . importing table                         "DEPT"          7 rows imported
. . importing table                        "DUMMY"          1 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                     "SALGRADE"          5 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 18:52:12 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn xyz/xyz
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
DUMMY                          TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 rolling        hyderabad
        60 ebus           ananthapur
        70 subbu          rajahmundry

7 rows selected.




No comments:

Post a Comment