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

[Jack C. Mahaffey]

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.

[Brian_274]

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]

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]

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 · Accepted solution]

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

[Jack C. Mahaffey]

Nice :) Thanks...