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:
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';
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');
Post a Comment