Search This Blog

Total Pageviews

Friday 14 July 2017

Oracle View metadata



Oracle View Metadata  ..




SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/


SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, owner) FROM   dba_views
WHERE  1=1
AND view_name = '&View_name';




SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/

SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, owner)
FROM   dba_views
WHERE  owner      = UPPER('&1')
AND    view_name = DECODE(UPPER('&2'), 'ALL', view_name, UPPER('&2'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON





set long 200000 pages 0 lines 150
select dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner)) 
from   all_views u
where 1=1
and  owner IN ('SYS') 
--and owner IN ('ANUJ') 
and view_name='ALL_IND_STATISTICS'
order by owner,view_name ;

Or 

select
  dbms_metadata.get_ddl('VIEW', 'ALL_IND_STATISTICS') stmt
from
  dual;

2 comments:

Anuj Singh said...

set long 5000 pagesize 500 linesize 200
select dbms_metadata.get_ddl('VIEW','&VIEW_NAME','&OWNER_NAME') from dual;

set long 1000 pagesize 500 linesize 200
select TEXT from DBA_VIEWS
where OWNER = '&OWNER_NAME'
and VIEW_NAME = '&VIEW_NAME';



Anuj Singh said...

set long 300000
col text for a100
select text from dba_views where view_name = upper('&view');
select query from dba_mviews where mview_name = upper('&view');
select view_definition from v$fixed_view_definition where view_name = upper('&view');

Oracle DBA

anuj blog Archive