Home » RDBMS Server » Backup & Recovery » How to Export & Import a Tablespace and part of a table
How to Export & Import a Tablespace and part of a table [message #126107] Fri, 01 July 2005 00:35 Go to next message
ksst
Messages: 10
Registered: February 2005
Junior Member
Hi,

I want to know how to export a part of a table using RMAN and also from the command prompt. How will you specify the condition where certain rows qualify for the export.

Also how does one take a complete export of and existing tablespace with all its objects. Would it be better to do this when the tablespace is online.

Could someone help out with this, please.

Thanks in advance,
Sri Sai Krishna
Re: How to Export & Import a Tablespace and part of a table [message #126113 is a reply to message #126107] Fri, 01 July 2005 01:37 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

hi,

Export using command prompt :


c:\>exp userid=scott/tiger File=exp.dmp tables=emp
query="""where id>10 and sal<=5000"""

This command will export rows from the table satisfying the condition in the query.

RMAN is used for Backup & recovery mainly , i m not sure that RMAN can be used for export.


Whenever u want to perform export/import of a tablespace then it must be online but in READ ONLY mode , i rpt READ ONLY mode, other wise u get an error.
And for exporting a tablespace user should only be 'sys'.

Try the following command for tablespace export with objects :

c:>exp userid='sys/passwd as sysdba' tablespaces=tbs1 file=exp.dmp log=tbs.log

regards,
Tarun

Re: How to Export & Import a Tablespace and part of a table [message #126171 is a reply to message #126113] Fri, 01 July 2005 06:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
tarundba wrote:
>> Whenever u want to perform export/import of a tablespace then it must be online but in READ ONLY mode , i rpt READ ONLY mode, other wise u get an error.
>>And for exporting a tablespace user should only be 'sys'.

What error?
It is not a hard rule.
I beleive, you said the tablespace has to be read-only during export , so that the tablespace can be consistent. export with CONSISTENT=Y should do the job for you.
During import, the tablespace cannot be in read-only mode.
(You cannot create the object when tablespace is readonly. right?).
Please see the following session.
and using 'sys' for any normal database operations is not too good.
You can export / import as any user.
Please see session.
scott@9i > !exp scott/tiger owner=scott file=scott.dmp statistics=none

Export: Release 9.2.0.4.0 - Production on Fri Jul 1 06:14:43 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP          0 rows exported
. . exporting table                        MYTABLE          0 rows exported
. . exporting table                     PLAN_TABLE          0 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 without warnings.

scott@9i > @clean_schema
Enter value for options_e_or_s: e
old   6: options:=upper('&options_E_or_S');
new   6: options:=upper('e');
Enter value for owner: scott
old  12:                where   owner=upper('&owner')
new  12:                where   owner=upper('scott')
drop TABLE  SCOTT.PLAN_TABLE cascade constraints
drop TABLE  SCOTT.MYTABLE cascade constraints
drop TABLE  SCOTT.EMP cascade constraints
drop TABLE  SCOTT.DEPT cascade constraints
drop SEQUENCE  SCOTT.MYSEQUENCE

scott@9i > !imp scott/tiger fromuser=scott touser=scott file=scott.dmp

Import: Release 9.2.0.4.0 - Production on Fri Jul 1 06:16:30 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR"
 "CHAR2(13))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1"
 "048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . importing table                         "DEPT"
IMP-00058: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "EMP" ("EMPNO" NUMBER, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9)"
 ", "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7,"
 " 2), "DEPTNO" NUMBER(2, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 "
 "STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" L"
 "OGGING NOCOMPRESS"
. . importing table                          "EMP"          0 rows imported
IMP-00017: following statement failed with ORACLE error 942:
 "CREATE INDEX "MYINDEX" ON "EMP" ("EMPNO" )  PCTFREE 10 INITRANS 2 MAXTRANS "
 "255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER"
 "S" LOGGING"
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "MYTABLE" ("RECORD_ID" VARCHAR2(999), "DATE_TIME" DATE, "FIELD"
 "2" VARCHAR2(100), "FROM_NUMBER" VARCHAR2(100), "TO_NUMBER" VARCHAR2(100))  "
 "PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576 FREEL"
 "ISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . importing table                      "MYTABLE"          0 rows imported
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "PLAN_TABLE" ("STATEMENT_ID" VARCHAR2(30), "TIMESTAMP" DATE, ""
 "REMARKS" VARCHAR2(80), "OPERATION" VARCHAR2(30), "OPTIONS" VARCHAR2(255), ""
 "OBJECT_NODE" VARCHAR2(128), "OBJECT_OWNER" VARCHAR2(30), "OBJECT_NAME" VARC"
 "HAR2(30), "OBJECT_INSTANCE" NUMBER(*,0), "OBJECT_TYPE" VARCHAR2(30), "OPTIM"
 "IZER" VARCHAR2(255), "SEARCH_COLUMNS" NUMBER, "ID" NUMBER(*,0), "PARENT_ID""
 " NUMBER(*,0), "POSITION" NUMBER(*,0), "COST" NUMBER(*,0), "CARDINALITY" NUM"
 "BER(*,0), "BYTES" NUMBER(*,0), "OTHER_TAG" VARCHAR2(255), "PARTITION_START""
 " VARCHAR2(255), "PARTITION_STOP" VARCHAR2(255), "PARTITION_ID" NUMBER(*,0),"
 " "OTHER" LONG, "DISTRIBUTION" VARCHAR2(30), "CPU_COST" NUMBER(*,0), "IO_COS"
 "T" NUMBER(*,0), "TEMP_SPACE" NUMBER(*,0), "ACCESS_PREDICATES" VARCHAR2(4000"
 "), "FILTER_PREDICATES" VARCHAR2(4000))  PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
 "XTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1) TABLESPAC"
 "E "USERS" LOGGING NOCOMPRESS"
. . importing table                   "PLAN_TABLE"          0 rows imported
Import terminated successfully with warnings.

----------------------------
where 
ORA-01647 tablespace 'string' is read only, cannot allocate space in it

    Cause: An attempt was made to allocate space in a read-only tablespace.

    Action: Create the object in another tablespace
----------------------------




Re: How to Export & Import a Tablespace and part of a table [message #126189 is a reply to message #126171] Fri, 01 July 2005 07:36 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


hi ,

OK mahesh u r correct. Read only is a pre requirement when we want to export a tablespace with transportable tablespace feature. If we use transportable tablespace feature without making tablespace read only then it will generate error.

D:\>exp userid='sys/password as sysdba' file=tbsexp.dmp tablespac
es=tanu transport_tablespace=y

Export: Release 9.2.0.1.0 - Production on Fri Jul 1 16:57:58 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29335 encountered
ORA-29335: tablespace 'TANU' is not read only
ORA-06512: at "SYS.DBMS_PLUGTS", line 436
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully


thanks,
Tarun
Re: How to Export & Import a Tablespace and part of a table [message #126190 is a reply to message #126189] Fri, 01 July 2005 07:44 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Yup.
My response was becuase
Posting from OP / yourself has not mentioned anything about transportable tablespace.
>>c:>exp userid='sys/passwd as sysdba' tablespaces=tbs1 file=exp.dmp log=tbs.log


Previous Topic: Urgent (Script )
Next Topic: Question abour restoring to diff. Server via RMAN
Goto Forum:
  


Current Time: Thu Mar 28 18:09:26 CDT 2024