Home » RDBMS Server » Backup & Recovery » 1 table Import from Full User Backup
1 table Import from Full User Backup [message #218545] Thu, 08 February 2007 10:43 Go to next message
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 #218579 is a reply to message #218545] Thu, 08 February 2007 14:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
import the dump into another temporary schema/database and do a CTAS or insert into target_table select * from table_name@temporary_schema/database .
Re: 1 table Import from Full User Backup [message #218592 is a reply to message #218579] Thu, 08 February 2007 21:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: 1 table Import from Full User Backup [message #218784 is a reply to message #218592] Sat, 10 February 2007 03:49 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Importing the whole dump really is not possible
I should have been more explicit. What i meant is, import just the table into another schema, which is now demonstrated.
Previous Topic: Exp hangs. unable to take backup. Need help
Next Topic: Diff Between Clone and standby Database
Goto Forum:
  


Current Time: Tue Nov 26 20:45:18 CST 2024