Solved

Import views

Posted on 2012-04-04
4
365 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

635 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