Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Restore views in oracle

Posted on 2010-09-03
21
Medium Priority
?
850 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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
 
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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

581 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