Oracle Technologies Blog

By ASKM

Archive for the ‘export/import utils’ Category

oracle exp/imp and expdp/impdp

Posted by Srikrishna Murthy Annam on August 24, 2010

If you dont know how to use the exact options of the exp/imp and expdp/impdp utilities for your requirement, here is a front end tool to use

http://www.alderprogs.com/

It is a licenced software but you can install the trial version to test.

Hope it helps

–SRI

Advertisements

Posted in export/import utils | Tagged: , , , , | Leave a Comment »

exporting objects to different schema with different tablespace

Posted by Srikrishna Murthy Annam on August 22, 2010

Assume that we have some objects in a schema TEST1 with default tablespace TS1. Suppose if there is an application running on this database which requires these objects to be in two different schemas.
Say the second schema is TEST2 with default tablespace TS2.
Now we have to move some objects from TEST1 schema to TEST2 schema.

Consider the following syntax ..

SQL>alter table TEST1.TABLE1 rename to TEST2.TABLE1; ==> Syntax error.
sql>alter table TEST1.TABLE1 move tablespace TS2; ==> This moves the object to tablespace TS2, but still in schema TEST1.

Does export and import work ?   Lets try ….

$exp system/manager file=TABLE1.dmp tables=TEST1.TABLE1 log=exp_TABLE1.log
$imp system/manager file=TABLE1.dmp fromuser=TEST1 touser=TEST2 tables=TABLE1 log=imp_TABLE1.log

This should actually move the table TABLE1 from TEST1 to TEST2.
But if you observe the table TABLE1 after the import it is in schema TEST2 which is ok but tablespace is still TS1. WHY ?
The user TEST2 has default tablespace TS2, so when we import the table it should be created in TS2 tablespace. Why is it created in TS1?

Then what is the solution to move an object to TEST2 with tablespace TS2. !!!!!!!

Follow the following steps to move the objects to different schema with different tablespace.

SQL>alter user TEST2 quota 0 on TS1 quota unlimited on TS2;
SQL>revoke unlimited tablespace from TEST2;

$imp system/manager file=TABLE1.dmp fromuser=TEST1 touser=TEST2 tables=TABLE1 log=imp_TABLE1.log

Then verify the object schema and tablespace. It should be TEST2 with TS2.

Hope it helps ….

— SRI


Posted in export/import utils | Tagged: , | 3 Comments »

Exporting/Importing table partitions

Posted by Srikrishna Murthy Annam on August 20, 2010

This article shows the table partition management and also how to export and import the table partitions.

SQL> CREATE TABLE “SH”.”PART_TABLE”
2     (       “PARAMETER” VARCHAR2(32) NOT NULL ENABLE,
3     “TIMESTAMP” NUMBER NOT NULL ENABLE,
4     “VALUESUM” NUMBER NOT NULL ENABLE,
5     “VALUECOUNT” NUMBER DEFAULT 1 NOT NULL ENABLE,
6      CONSTRAINT “PK_INDEX01” PRIMARY KEY (“PARAMETER”, “TIMESTAMP”) ENABLE,
7      CONSTRAINT “VALUECOUNT_POS” CHECK ( valuecount > 0 ) ENABLE
8     ) ORGANIZATION INDEX COMPRESS 1 PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING
9    TABLESPACE “TS1”
10    STORAGE(
11    BUFFER_POOL DEFAULT)
12   PCTTHRESHOLD 50
13    PARTITION BY RANGE (“TIMESTAMP”)
14   (PARTITION “PART_1”  VALUES LESS THAN (1277596800001)
15    PCTFREE 10 INITRANS 2 MAXTRANS 255
16    STORAGE(INITIAL 50331648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
17    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
18    TABLESPACE “TS1” );

Table created.

SQL> select segment_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select partition_name,tablespace_name from dba_segments where segment_name=’PART_TABLE’;

no rows selected

SQL> select table_name,tablespace_name from dba_tables where table_name=’PART_TABLE’;

TABLE_NAME                     TABLESPACE_NAME
—————————— ——————————
PART_TABLE

SQL> select partition_name,tablespace_name from dba_tab_partitions where partition_name=’PART_1′;

PARTITION_NAME                 TABLESPACE_NAME
—————————— ——————————
PART_1

SQL> select index_name,partition_name,subpartition_count,status from dba_ind_partitions where tablespace_name=’TS1′;

INDEX_NAME                     PARTITION_NAME                 SUBPARTITION_COUNT STATUS
—————————— —————————— —————— ——–
PK_INDEX01                     PART_1                               0 USABLE

SQL> select owner,table_name,index_name,status from dba_indexes where index_name=’PK_INDEX01′;

OWNER      TABLE_NAME                     INDEX_NAME              STATUS
———- —————————— ——————————
SH         PART_TABLE                     PK_INDEX01              N/A

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1′ group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             1

SQL> ALTER TABLE SH.PART_TABLE ADD
2    PARTITION “PART_2”  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1” ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select segment_type,count(1) from dba_segments where tablespace_name=’TS1′ group by segment_type;

SEGMENT_TYSH         COUNT(1)
—————— ———-
INDEX PARTITION             2

Insert some  data

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971

$exp SH/SH@RACDB file=part3.dmp log=part3_exp.log tables=SH.PART_TABLE:PART_2 feedback=10000 statistics=none constraints=n

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: constraints on tables will not be exported

About to export sSHcified tables via Conventional Path …
. . exporting table PART_TABLE
. . exporting partition              PART_2
…………………………………………………………………
…..
809310 rows exported
Export terminated successfully without warnings.

SQL> alter table SH.PART_TABLE drop partition PART_2;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

SQL>    alter table SH.PART_TABLE add
2    PARTITION “PART_2”  VALUES LESS THAN (1282910400001)
3     PCTFREE 10 INITRANS 2 MAXTRANS 255
4     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6    TABLESPACE “TS1” ;

Table altered.

SQL> SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=’PART_TABLE’ and PARTITION_NAME like ‘PART_%’ ORDER BY PARTITION_NAME;

PARTITION_NAME
——————————
PART_1
PART_2

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
699448661

$ imp SH/SH@RACDB file=part3.dmp log=part3_imp.log fromuser=SH buffer=200000 recordlength=6400 tables=PART_TABLE:PART_2 feedback=10000 ignore=y constraints=n statistics=none

Export: Release 10.2.0.4.0 – Production on Thu Aug 26 07:27:56 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

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

import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SH’s objects into SH
. . importing partition “PART_TABLE”:”PART_2″
…………………………………………………………………
…..
809310 rows imported
Import terminated successfully without warnings.

SQL> select count(1) from SH.PART_TABLE;

COUNT(1)
———-
700257971

Hope it helps …
–SRI

Posted in Database Articles, export/import utils | Tagged: , , , , , | Leave a Comment »

 
%d bloggers like this: