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  |
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  |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 31 10:54:10 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
