Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle sqlplus script for listing dba_views... Want compete text.

SOLVED
Go to solution
Jack C. Mahaffey
Super Advisor

Oracle sqlplus script for listing dba_views... Want compete text.

Anybody got a sqlplus script for dumping the database views for a particuler owner. I wan't to keep a snapshot of all views for an owner that can be compared to other databases.
5 REPLIES
Brian_274
Frequent Advisor

Re: Oracle sqlplus script for listing dba_views... Want compete text.

select owner, name, text from dba_views will get you the structure to recreate the view. But since the text column is a long it's a pain to work with. But you might want to look into buying TOAD from QUEST. Software www.quest.com. This costs about $700 and it's a great little tool. It has other add ons you can buy. You can export the source code to recreate any database object you want. Or all the objects for a specific user. You can also run a compare on schemas with this tool. or a file compare. But I think this would do exactly what you want and more. Heck you could even just download a free trial version for a month and do this to see if it's what you want.
Jack C. Mahaffey
Super Advisor

Re: Oracle sqlplus script for listing dba_views... Want compete text.

Not really into buying more tools. Here's something that will work.

-- --------------------------------
set echo off
SET head OFF
SET verify OFF
SET feedback OFF
SET newpage 0
SET pagesize 0
set long 30000;

select 'CREATE OR REPLACE VIEW ' || OWNER || '.' || VIEW_NAME || ' AS ' , text , ';'
from dba_views
where owner = 'MSDDBA'
ORDER BY view_name asc;


-- --------------------------------

I'll likely spool the output for compares.

thanks... jack...
Brian_274
Frequent Advisor

Re: Oracle sqlplus script for listing dba_views... Want compete text.

I understand. I hate gui tools also. But toad is a great low cost tool that will pretty much do anything you'd ever want.
Yogeeraj_1
Honored Contributor
Solution

Re: Oracle sqlplus script for listing dba_views... Want compete text.

hi Jack,

try these:

getaview gets one view to a file named "viewname.sql"

--------------- getaview.sql ------------------------
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
set long 50000

column column_name format a1000
column text format a1000

spool &1..sql
prompt create or replace view &1 (
select decode(column_id,1,'',',') || column_name column_name
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt ) as
select text
from user_views
where view_name = upper('&1')
/
prompt /
spool off

set heading on
set feedback on
set verify on
set termout on
---------------------------------------------------


To get all views in a schema, you could use getallviews.sql:

------------ getallviews.sql ------------------------------

set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on

spool tmp.sql
select '@getaview ' || view_name
from user_views
/
spool off

set termout on
set heading on
set feedback on
set verify on
@tmp
----------------------------------------

Hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jack C. Mahaffey
Super Advisor

Re: Oracle sqlplus script for listing dba_views... Want compete text.

Nice :) Thanks...