oracle export partition wise from one database to another database

[oracle@linux251 ~]$ export ORACLE_SID=dwh02
[oracle@linux251 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:27:07 2013

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


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

SQL> alter tablespace TEST_TABLESPACE_2 read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux251 ~]$ expdp  transportable=always directory=bkp dumpfile=part_part.dmp tables=SCOTT.PART_PART:P0032,SCOTT.PART_PART:P0033,SCOTT.PART_PART:P0034,SCOTT.PART_PART:P0035,SCOTT.PART_PART:P0036,SCOTT.PART_PART:P0037,SCOTT.PART_PART:P0038,SCOTT.PART_PART:P0039,SCOTT.PART_PART:P0040,SCOTT.PART_PART:P0041,SCOTT.PART_PART:P0042,SCOTT.PART_PART:P0043,SCOTT.PART_PART:P0044,SCOTT.PART_PART:P0045,SCOTT.PART_PART:P0046,SCOTT.PART_PART:P0047,SCOTT.PART_PART:P0048,SCOTT.PART_PART:P0049,SCOTT.PART_PART:P0050,SCOTT.PART_PART:P0051,SCOTT.PART_PART:P0052,SCOTT.PART_PART:P0053,SCOTT.PART_PART:P0054,SCOTT.PART_PART:P0055,SCOTT.PART_PART:P0056,SCOTT.PART_PART:P0057,SCOTT.PART_PART:P0058,SCOTT.PART_PART:P0059,SCOTT.PART_PART:P0060,SCOTT.PART_PART:P0061,SCOTT.PART_PART:P0062,SCOTT.PART_PART:P0063,SCOTT.PART_PART:P0064,SCOTT.PART_PART:P0065,SCOTT.PART_PART:P0066,SCOTT.PART_PART:P0067,SCOTT.PART_PART:P0068,SCOTT.PART_PART:P0069,SCOTT.PART_PART:P0070

Export: Release 11.2.0.3.0 - Production on Fri Nov 29 11:28:27 2013

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  sys/******** AS SYSDBA transportable=always directory=bkp dumpfile=part_part.dmp tables=SCOTT.PART_PART:P0032,SCOTT.PART_PART:P0033,SCOTT.PART_PART:P0034,SCOTT.PART_PART:P0035,SCOTT.PART_PART:P0036,SCOTT.PART_PART:P0037,SCOTT.PART_PART:P0038,SCOTT.PART_PART:P0039,SCOTT.PART_PART:P0040,SCOTT.PART_PART:P0041,SCOTT.PART_PART:P0042,SCOTT.PART_PART:P0043,SCOTT.PART_PART:P0044,SCOTT.PART_PART:P0045,SCOTT.PART_PART:P0046,SCOTT.PART_PART:P0047,SCOTT.PART_PART:P0048,SCOTT.PART_PART:P0049,SCOTT.PART_PART:P0050,SCOTT.PART_PART:P0051,SCOTT.PART_PART:P0052,SCOTT.PART_PART:P0053,SCOTT.PART_PART:P0054,SCOTT.PART_PART:P0055,SCOTT.PART_PART:P0056,SCOTT.PART_PART:P0057,SCOTT.PART_PART:P0058,SCOTT.PART_PART:P0059,SCOTT.PART_PART:P0060,SCOTT.PART_PART:P0061,SCOTT.PART_PART:P0062,SCOTT.PART_PART:P0063,SCOTT.PART_PART:P0064,SCOTT.PART_PART:P0065,SCOTT.PART_PART:P0066,SCOTT.PART_PART:P0067,SCOTT.PART_PART:P0068,SCOTT.PART_PART:P0069,SCOTT.PART_PART:P0070
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/part_part.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST_TABLESPACE_2:
  /data/dwh02/datafiles/test_tablespace_2.dbf
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 11:28:45

[oracle@linux251 ~]$ cp /data/dwh02/datafiles/test_tablespace_2.dbf /data/dwh04/dwh04/test_tablespace_2.dbf
[oracle@linux251 ~]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:28:59 2013

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


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

SQL> alter tablespace TEST_TABLESPACE_2 read write;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@linux251 ~]$ export ORACLE_SID=dwh04
[oracle@linux251 ~]$ impdp  PARTITION_OPTIONS=departition TRANSPORT_DATAFILES='/data/dwh04/dwh04/test_tablespace_2.dbf' DIRECTORY=bkp DUMPFILE=part_part.dmp

Import: Release 11.2.0.3.0 - Production on Fri Nov 29 11:30:03 2013

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

Username: sys as sysdba
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  sys/******** AS SYSDBA PARTITION_OPTIONS=departition TRANSPORT_DATAFILES=/data/dwh04/dwh04/test_tablespace_2.dbf DIRECTORY=bkp DUMPFILE=part_part.dmp
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:30:23

[oracle@linux251 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 29 11:32:31 2013

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


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

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
PART_PART_P0032                TABLE
PART_PART_P0033                TABLE
PART_PART_P0034                TABLE
PART_PART_P0035                TABLE
PART_PART_P0036                TABLE
PART_PART_P0037                TABLE
PART_PART_P0038                TABLE
PART_PART_P0039                TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PART_PART_P0040                TABLE
PART_PART_P0041                TABLE
PART_PART_P0042                TABLE
PART_PART_P0043                TABLE
PART_PART_P0044                TABLE
PART_PART_P0045                TABLE
PART_PART_P0046                TABLE
PART_PART_P0047                TABLE
PART_PART_P0048                TABLE
PART_PART_P0049                TABLE
PART_PART_P0050                TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PART_PART_P0051                TABLE
PART_PART_P0052                TABLE
PART_PART_P0053                TABLE
PART_PART_P0054                TABLE
PART_PART_P0055                TABLE
PART_PART_P0056                TABLE
PART_PART_P0057                TABLE
PART_PART_P0058                TABLE
PART_PART_P0059                TABLE
PART_PART_P0060                TABLE
PART_PART_P0061                TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PART_PART_P0062                TABLE
PART_PART_P0063                TABLE
PART_PART_P0064                TABLE
PART_PART_P0065                TABLE
PART_PART_P0066                TABLE
PART_PART_P0067                TABLE
PART_PART_P0068                TABLE
PART_PART_P0069                TABLE
PART_PART_P0070                TABLE
SALGRADE                       TABLE

43 rows selected.

Comments

Post a Comment

Popular posts from this blog

Installing DBMS_JAVA package in Oracle and calling UTL_DBWS web services through Oracle database

Installing DBMS_JAVA package in Oracle following  files need to be run:-  sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; @?/javavm/install/initjvm.sql; @?/xdk/admin/initxml.sql; @?/xdk/admin/xmlja.sql; @?/rdbms/admin/catjava.sql; @?/rdbms/admin/catexf.sql; and verify your java installation in Oracle Database  sql> select comp_name, version, status from dba_registry where comp_name like '%JAVA%'; sql> select dbms_java.longname(object_name), status, object_type from all_objects where       object_type = 'JAVA CLASS'; ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ calling  UTL_DBWS  web services through Oracle Database :-  set the follwing parameters in .bash_profile of oracle user  export JAVA_HOME=$ORACLE_HOME/jdk ORACLE_BASE=/opt/oracle/product; export ORACLE_BASE ORACLE_HOME=/opt/oracle/product/11.2.0/dbhome_1; exp...
Read more

stick the job to the specific instance in Oracle RAC

First method ( and easy one too ) :- note:-  must perform this activity on each instance one by one. ......stop jobs which is assigning to the specific instance..... $ export ORACLE_SID=SID connect database using job owner .... $ sqlplus username/password list of jobs and its instance....  sql> select JOB,INSTANCE from user_jobs;        JOB   INSTANCE ---------- ----------       2510          0       2951          0       2992          0       3091          0       3111          0       1570          0       1567          0       1568          0       1569     ...
Read more

finding fragmentation at table level and tablespace level in Oracle and steps to remove them

Use this query :- select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",     round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",     round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"     from all_tables  WHERE Owner NOT IN (        'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',        'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',        'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',        'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',        'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',        'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',        'SI_INFORMTN_SCHEMA', 'XDB', 'ODM'); Or This one :- It will collect the data which ...
Read more