Tuesday 30 August 2016

How to take the backup at tablespaces level using export/import utility?

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
ROOM

6 rows selected.

SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM                     USERDATA
DBSNMP                                SYSAUX
DIP                                         USERDATA
APPQOSSYS                         SYSAUX
SYSTEM                                SYSTEM
SYS                                         SYSTEM
OUTLN                                  SYSTEM
ABC                                       USERDATA

8 rows selected.

SQL> alter user abc default tablespace room;

User altered.

SQL> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM                     USERDATA
DBSNMP                                SYSAUX
DIP                                         USERDATA
APPQOSSYS                         SYSAUX
SYSTEM                                SYSTEM
SYS                                        SYSTEM
OUTLN                                 SYSTEM
ABC                                       ROOM

8 rows selected.

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

6 rows selected.

[oracle@appsdb exp]$ exp file=tablespace.dmp log=tablespace.log tablespaces=room

Export: Release 11.2.0.1.0 - Production on Tue Aug 30 18:27:16 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 done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export selected tablespaces ...
For tablespace ROOM ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
ROOM

6 rows selected.

SQL> drop tablespace room including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA

here the tablespace room is dropped  including contents and datafiles.

[oracle@appsdb exp]$ imp file=tablespace.dmp full=y

Import: Release 11.2.0.1.0 - Production on Tue Aug 30 18:37:58 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
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
Import terminated successfully without warnings.
SQL> conn abc/abc
Connected.
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

6 rows selected.





No comments:

Post a Comment