Solved

Restore views in oracle

Posted on 2010-09-03
21
781 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:yoyolut
  • 10
  • 7
  • 4
21 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33594900
but those views needs the underlying tables right ?
0
 

Author Comment

by:yoyolut
ID: 33594919
??

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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33594947
how many views are there ? why don't we take the scripts and run in the required database.
0
 

Author Comment

by:yoyolut
ID: 33594960
I have just one views to restore.

For the script, yes i Known, but we don't have the script... :-S
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33594984
select  * from dba_views and take the text or use sql developer or toad to generate the script for you right ?

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595026
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33595045
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595062
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595082
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
 

Author Comment

by:yoyolut
ID: 33595084
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
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.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595132
Did you test the script which i gave you to run in sql*plus ?
0
 

Author Comment

by:yoyolut
ID: 33595136
I have a Line lenght overfloo from dbms_output...
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595155
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
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 33595162
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
 

Author Comment

by:yoyolut
ID: 33595172
It's work fine, but it's the bad script of my views (The actual script which are not good )
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595176
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33595181
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
 

Author Comment

by:yoyolut
ID: 33595201
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33595217
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
 

Author Comment

by:yoyolut
ID: 33595235
Thanks Milleniumaire !
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 33595254
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

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

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

708 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

17 Experts available now in Live!

Get 1:1 Help Now