1 table Import from Full User Backup [message #218545] |
Thu, 08 February 2007 10:43 |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Hi,
we have a daily hotback ( RMAN ) and a every weekend full user level backup ( export ) by on
tape drive, from that full user level back up , i want only 1 table Reporting_DATA and import data in a new table reporting_data_compare how can i get that .
i am creating that new table reporting_Data_compare like this
create table reporting_Data_compare as select * from reporting_data where 1=0;
so as same structure table with no rows is created, now i want to import the data into this new table,
Basically we have an issue so want to compare the data from this table as of date,
How can i do this, or this any other better solution to do this task.
Thanks
|
|
|
|
Re: 1 table Import from Full User Backup [message #218592 is a reply to message #218579] |
Thu, 08 February 2007 21:07 |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Thanks Mahesh for follow up,
Importing the whole dump really is not possible , it's a 500-600 GB from production database, so i don't think i can create a empty schema to hold 600 GB database.
We just want to compare only 1 table, i know it's difficult to get only 1 table from whole dump, any other suggestions from your experience.
Thanks
|
|
|
Re: 1 table Import from Full User Backup [message #218769 is a reply to message #218592] |
Fri, 09 February 2007 23:32 |
Mohammad Taj
Messages: 2412 Registered: September 2006 Location: Dubai, UAE
|
Senior Member |
|
|
Hi,
Try below procedure.
#import your required table in another schema from whole dump file.
$imp system/oracle file=path log=path TABLES=your_required_table constraints=n grant=n indexes=n
FROMUSER=dump_file_owner_user TOUSER=new_user
#no need to create dummy structure you should create direct table with data through CTAS.
$create table yourolduser.TABNAME as select * from yourNEWuser.TABNAME;
---------------------------
I have scott schema dump file and i want to import only EMP table from whole dump file.
SQL> host exp system/oracle file=e:\scott.dmp OWNER=scott
...
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . exporting table EMP1 14 rows exported
. . exporting table EMP_COPY 0 rows exported
. . exporting table SALGRADE 5 rows exported
...
Export terminated successfully without warnings.
SQL> host imp system/oracle file=e:\scott.dmp TABLES=EMP -
> fromuser=SCOTT touser=SYSTEM -
> constraints=N indexes=N grants=N
Import: Release 10.1.0.2.0 - Production on Sat Feb 10 09:30:15 2007
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
. . importing table "EMP" 14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL> DROP TABLE SCOTT.EMP_COPY;
Table dropped.
SQL> create table SCOTT.EMP_COPY as select * from SYSTEM.EMP;
Table created.
regards
Taj
[Updated on: Fri, 09 February 2007 23:33] Report message to a moderator
|
|
|
|