Import views

Hi

How to import a particular view for a schema  in oracle using the full tradional export dump file?
bhanu823Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
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
bhanu823Author Commented:
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
DavidSenior Oracle Database AdministratorCommented:
imp scott/tiger IGNORE=y FILE = dat1.dmp full=n FROMUSER=sc1 TABLES=(view1)
0
Wasim Akram ShaikCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.