Solved

Import views

Posted on 2012-04-04
4
364 Views
Last Modified: 2012-05-01
Hi

How to import a particular view for a schema  in oracle using the full tradional export dump file?
0
Comment
Question by:bhanu823
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Expert Comment

by:David
ID: 37806713
Start with the docs.oracle.com for import, as this may depend upon the version from which the export was taken.  In particular,

Importing Views

Views are exported in dependency order. In some cases, Export must determine the ordering, rather than obtaining the order from the server database. In doing so, Export may not always be able to duplicate the correct ordering, resulting in compilation warnings when a view is imported, and the failure to import column comments on such views.

In particular, if viewa uses the stored procedure procb, and procb uses the view viewc, Export cannot determine the proper ordering of viewa and viewc. If viewa is exported before viewc and procb already exists on the import system, viewa receives compilation warnings at import time.

Grants on views are imported even if a view has compilation errors. A view could have compilation errors if an object it depends on, such as a table, procedure, or another view, does not exist when the view is created. If a base table does not exist, the server cannot validate that the grantor has the proper privileges on the base table with the GRANT OPTION. Access violations could occur when the view is used if the grantor does not have the proper privileges after the missing tables are created.

Importing views that contain references to tables in other schemas requires that the importer have SELECT ANY TABLE privilege. If the importer has not been granted this privilege, the views will be imported in an uncompiled state. Note that granting the privilege to a role is insufficient. For the view to be compiled, the privilege must be granted directly to the importer.

From: http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch02.htm
0
 

Author Comment

by:bhanu823
ID: 37806827
Hi

Here is the requireement.. There is a database called 'db1', under that there is a schema called 'sc1'. when I did refresh of this  dev database from the production ..some of the tables didn't come as they are not in production also. However there is a an export for the dev database. So, I have to import one view from the backup ( not datapump, tradional  exp/imp).. so how can I import the view from the ful database export to  schema 'sc1'?
0
 
LVL 23

Expert Comment

by:David
ID: 37806937
imp scott/tiger IGNORE=y FILE = dat1.dmp full=n FROMUSER=sc1 TABLES=(view1)
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 37808195
author, dvz had posted the command to do this task.. to be more specific.. you need a little bit of modification in the command..

if in the export file( the view is present in sc1 and you are importing in sc1 schema itself then you can use the below syntax) assuming sc1 user has import database grant..

imp <schema>/<password> file=<export_file.dmp> full=n tables=(<view_name>)

imp sc1/<password> IGNORE=y FILE = dat1.dmp full=n TABLES=(view1)


if the importing schema is different from schema in which view is present then you can use some system account

imp system/<password> file=dat1.dmp full=n tables=view1 fromuser=sc1 touser=<schema_name>
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 64
capture vmstat info and insert it into an oracle table 31 93
Oracle Nested table uses ? 2 60
oracle query 3 35
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question