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

Thanks,
Yo
yoyolutAsked:
Who is Participating?
 
MilleniumaireConnect With a Mentor Commented:
Ignore my earlier comment regarding the use of the indexfile option as I've tried this and it doesn't appear to include the DDL required to create views.

One way to restore the views from the export file would be to run an import, but choose to import into a different user (assuming you don't want to affect the data in the user account from which the export was performed).  You can choose the option to import no rows, which will then simply create the table, index and view definitions etc.  You can then extract the required view definitions from the USER_VIEWS table, which will give you the required code to build the view under any schema you wish.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
but those views needs the underlying tables right ?
0
 
yoyolutAuthor Commented:
??

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 ?

Thx,
Yo
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
how many views are there ? why don't we take the scripts and run in the required database.
0
 
yoyolutAuthor Commented:
I have just one views to restore.

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

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch02.htm#1006651

so if it is just one view creation, take the script manually and create it.
0
 
MilleniumaireCommented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
I tested this and it works fine for me. run this in SQL*PLUS

declare
a clob;
begin
 a:= DBMS_METADATA.GET_DDL ('VIEW','MYVIEW1');  --> change myview1 to your view name
dbms_output.put_line(a);
end;
/
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Look at this link and search for "DBMS_METADATA.GET_DDL" for all other parameters and extra information.

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1024701
0
 
yoyolutAuthor Commented:
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...
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Did you test the script which i gave you to run in sql*plus ?
0
 
yoyolutAuthor Commented:
I have a Line lenght overfloo from dbms_output...
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
ok.. may be your view text is of more length.

try this..it should work in toad or sqlplus etc...

select DBMS_METADATA.GET_DDL ('VIEW','MYVIEW1') xx from dual;
0
 
yoyolutAuthor Commented:
It's work fine, but it's the bad script of my views (The actual script which are not good )
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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%' ;
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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 ?
0
 
yoyolutAuthor Commented:
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 !
0
 
MilleniumaireCommented:
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

0
 
yoyolutAuthor Commented:
Thanks Milleniumaire !
0
 
MilleniumaireCommented:
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.
0
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.

All Courses

From novice to tech pro — start learning today.