Solved

Import views

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 100
levels for reporting 5 51
Need help converting some sql server sql to Oracle sql 9 61
Oracle TEXT search question 9 29
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now