Link to home
Start Free TrialLog in
Avatar of yoyolut
yoyolutFlag for France

asked on

Restore views in oracle

Hi All,

I mak an export full and i get a dmp file.

I want to restore one views, how can make this?  with imp tools ? (I can restore on table or user with imp but i don't see any views import

Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

but those views needs the underlying tables right ?
Avatar of yoyolut



Sorry i don't undertand your mind...

I deleted a views and i need to retore it, i have a dmp file, so my question is : "Is it possible" or need i to performe a full imp ?

how many views are there ? why don't we take the scripts and run in the required database.
Avatar of yoyolut


I have just one views to restore.

For the script, yes i Known, but we don't have the script... :-S
select  * from dba_views and take the text or use sql developer or toad to generate the script for you right ?

just look at this link and search for "Importing Views". They are exported on a dependency basis in the sense along with the underlying tables and their privileges as well.

so if it is just one view creation, take the script manually and create it.
The indexfile will write all the create statements in the specified file.  I would assume this also includes the views etc.  You can use it as follows:

imp file=exportfile.dmp indexfile=myfile.txt

For more details type:

imp help=y
I tested this and it works fine for me. run this in SQL*PLUS

a clob;
 a:= DBMS_METADATA.GET_DDL ('VIEW','MYVIEW1');  --> change myview1 to your view name
Look at this link and search for "DBMS_METADATA.GET_DDL" for all other parameters and extra information.
Avatar of yoyolut


Thanks, but in dba_views, i Have the actual script of my views, so it's not enought... (I delete the script and after i trie to restore by a script but is not good...)

I look at the website, but no script or no explaination about restoring one views...
Did you test the script which i gave you to run in sql*plus ?
Avatar of yoyolut


I have a Line lenght overfloo from dbms_output...
ok.. may be your view text is of more length.

try should work in toad or sqlplus etc...

select DBMS_METADATA.GET_DDL ('VIEW','MYVIEW1') xx from dual;
Avatar of Milleniumaire
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yoyolut


It's work fine, but it's the bad script of my views (The actual script which are not good )
in generic, this works. Change the where clause accordingly.

select view_name,
DBMS_METADATA.GET_DDL ('VIEW',view_name) xx from dba_views
where owner in ('SCOTT')
and name like '%PAY%' ;
but it's the bad script of my views (The actual script which are not good ) --> cannot understand.

Do you mean to say in some other database as well, you do not have the correct view which you can import ?
Avatar of yoyolut


Sorry, my english is bad (My Oracle too :-D )

I have a views, which is not good, because we make a mistace (Deleting in fact and after we try to create the same views with old script... )

So i need to restore a good views with my export full files...

When i exec your script, i have the bad script, not the good script which is deleting.

So we make a full import with the option no rows (As you said it...)

Thanks for your help !
Do you have a schema containing the correct view that you want?  (It sounds like you don't).  If not, then I don't see how you can recover the view using sql statements as suggested by nav_kum_v.  

If you know the correct view is in the export file then you need to recover it from that file using import.

As I understand it, the export file is a FULL export of the whole database.  If you know which user the view belonged to, then you can run import and simply import ALL objects for that user.  Assuming all the objects already exist, except the view, providing you choose the option to NOT import rows and to ignore errors, then the result will be that the only object imported will be the missing view.

If there may be other objects missing these will also be imported.  If you don't want to risk this, then create a new oracle user and use the method I suggested earlier to import the definitions of the objects, but not the table data.  You can then use the sql script provided by nav_kum_v to run against that new schema to extract the view definition.

Using:  imp=help=y   will show you the various options you can supply to import on the command line.  The ones in particular are:

imp userid=username/password file=exportfile.dmp fromuser=myuser ignore=y rows=n grants=n indexes=n

Avatar of yoyolut


Thanks Milleniumaire !
Okay, no problem.  I've just tried this and it worked for me.  There's also the constraints=n option as well that I missed from my last post, the idea being to import as little as possible, but to get the views.