Solved

Import views

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

809 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