Oracle DBMS_METADATA ....
https://anuj-singh.blogspot.com/2018/01/oracle-profile-metadata.html https://anuj-singh.blogspot.com/2012/04/oracle-job-metadata.html
from https://github.com/oraclebase/dba/blob/master/
--- Table and index 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;
/
define tab='XXXXXXX'
SELECT DBMS_METADATA.get_ddl ('TABLE', TABLE_name, owner)
FROM all_tables
WHERE 1=1
--and owner = UPPER('1')
AND table_name = '&tab'
;
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM all_indexes
WHERE 1=1
--and owner = UPPER('1')
AND table_name = '&tab'
;SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 80 LINESIZE 100 FEEDBACK OFF VERIFY OFF TRIMSPOOL ONdefine O='ANUJ'
define sequence_name='S'
SET LONG 50000 LONGCHUNKSIZE 50000 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 ('SEQUENCE', sequence_name, sequence_owner)
FROM all_sequences
WHERE sequence_owner = UPPER('&O')
AND sequence_name = DECODE(UPPER('&sequence_name'), 'ALL', sequence_name, UPPER('&sequence_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
===========
define O='ANUJ'
define SYNONYM='S'
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 ('SYNONYM', synonym_name, owner)
FROM all_synonyms
WHERE owner = UPPER('&O')
--AND synonym_name = DECODE(UPPER('&SYNONYM'), 'ALL', synonym_name, UPPER('&SYNONYM'))
;
SET PAGESIZE 14 FEEDBACK ON VERIFY ON
=================
define O='ANUJ'
define table_name='EMP'
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 ('CONSTRAINT', constraint_name, owner)
FROM all_constraints
WHERE owner = UPPER('&O')
AND table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
AND constraint_type IN ('U', 'P');
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
============================
define O='ANUJ'
define table_name='EMP'
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);
-- Uncomment the following lines if you need them.
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE owner = UPPER('&O')
AND table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
====================================
define O='ANUJ'
define OBJECT_GRANT='EMP'
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_dependent_ddl('OBJECT_GRANT', UPPER('&OBJECT_GRANT'), UPPER('&O')) from dual
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
======================================
define O='ANUJ'
define table_name='EMP'
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);
-- Uncomment the following lines if you need them.
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
--DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/
SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM all_indexes
WHERE owner = UPPER('&O')
AND table_name = DECODE(UPPER('&table_name'), 'ALL', table_name, UPPER('&table_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
===
define O='ANUJ'
define table_name='EMP'
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 ('TRIGGER', trigger_name, owner)
FROM all_triggers
WHERE table_owner = UPPER('&O')
AND table_name = UPPER('&table_name');
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
==
define tablespace_name='SYSTEM'
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 ('TABLESPACE', tablespace_name)
FROM dba_tablespaces
WHERE tablespace_name = DECODE(UPPER('&tablespace_name'), 'ALL', tablespace_name, UPPER('&tablespace_name'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
====
define O='ANUJ'
define trigger_name='T'
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 ('TRIGGER', trigger_name, owner)
FROM all_triggers
WHERE owner = UPPER('&O')
AND trigger_name = DECODE(UPPER('&trigger_name'), 'ALL', trigger_name, UPPER('&trigger_name'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
===
define O='ANUJ'
define view_name='T'
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 all_views
WHERE owner = UPPER('&O')
AND view_name = DECODE(UPPER('&view_name'), 'ALL', view_name, UPPER('&view_name'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
========define v_owner='ANUJ'
define v_table_name='TEST'
set feed off term off
select upper('&2') v_table_name from dual;
set feed on term on
var v_owner varchar2(30)
var v_table_name varchar2(30)
begin
:v_owner := '&&v_owner';
:v_table_name := '&&v_table_name';
end;
/
set pagesize 50000 linesize 200 trimspool on long 2000000
col ddl format a150
begin
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE', TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
end;
/
set pagesize 500 linesize 200 trimspool on
col ddl format a125
prompt set echo on
prompt --########################################
prompt --## TABLE DDL
prompt --########################################
select replace(dbms_metadata.get_ddl('TABLE',:v_table_name,:v_owner),'"','') ddl from dual
/
prompt --########################################
prompt --## INDEX DDL
prompt --########################################
select replace(dbms_metadata.get_ddl('INDEX',i.index_name, i.owner),'"','') ddl
from dba_indexes i
where i.owner = :v_owner
and i.table_name = :v_table_name
/
prompt --########################################
prompt --## PRIMARY KEY
prompt --########################################
select replace(dbms_metadata.get_ddl('CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'P'
/
prompt --########################################
prompt --## FOREIGN KEYS
prompt --########################################
select replace(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'R'
/
prompt --########################################
prompt --## CHECK CONSTRAINTS
prompt --########################################
select replace(dbms_metadata.get_ddl('CONSTRAINT',c.constraint_name, c.owner),'"','') ddl
from dba_constraints c
where c.owner = :v_owner
and c.table_name = :v_table_name
and c.constraint_type = 'C'
and c.generated not like 'GENERATED%'
/
undef 1 2
============
Index metadata !!!
set long 5000 pagesize 0
select DBMS_METADATA.GET_DDL('INDEX', index_name,owner)
from all_indexes
where table_name in ('XXX')
and owner='XXXX'
;
====
---materialized view
from Web--
SET serveroutput on feedback off
UNDEF v_sql
define ENTER_MVIEW_OWNER=ANUJ'
define ENTER_MVIEW_NAME='XXX_MVIEW'
DECLARE
v_task_name VARCHAR2 (100);
v_mview_owner VARCHAR2 (30) := UPPER ('&&ENTER_MVIEW_OWNER');
v_mview_name VARCHAR2 (30) := UPPER ('&&ENTER_MVIEW_NAME');
v_mview_sql VARCHAR2 (4000);
v_mview_log_sql VARCHAR2 (4000);
BEGIN
-- get mview text from data dictionary
SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
INTO v_mview_sql
FROM dba_mviews
WHERE owner = v_mview_owner
AND mview_name = v_mview_name
;
SELECT DBMS_METADATA.get_dependent_ddl ('MATERIALIZED_VIEW_LOG',
referenced_name,
referenced_owner
)
INTO v_mview_log_sql
FROM dba_dependencies
WHERE referenced_type = 'TABLE'
AND referenced_name != v_mview_name
AND owner = v_mview_owner
AND NAME = v_mview_name
;
DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
DBMS_OUTPUT.put_line ('MVIEW LOG SQL Is: ' || v_mview_log_sql);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
=====
SET serveroutput on feedback off lines 400 long 999999
DECLARE
v_task_name VARCHAR2 (100);
v_mview_sql VARCHAR2 (4000);
v_mview_log_sql VARCHAR2 (4000);
cursor c1 is select mview_name from dba_mviews where owner='ANUJ';
BEGIN
FOR MV_REC IN C1
LOOP
SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', mview_name, owner)
INTO v_mview_sql
FROM dba_mviews
WHERE owner = 'ANUJ'
AND mview_name = MV_REC.mview_name;
DBMS_OUTPUT.put_line ('MVIEW SQL Is: ' || v_mview_sql);
END loop;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
===========================
-- PROCEDURE text
set linesize 400 pagesize 300
col name for a20
col TEXT for a50 wrap
SELECT NAME,TYPE
--,LINE
,TEXT FROM dba_source
WHERE TYPE = 'PROCEDURE'
and NAME='XXX'
and OWNER='ANUJ'
;
define O='XXX'
define T='EMP1'
SET LONG 10000 LONGCHUNKSIZE 10000 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 REPLACE(
DBMS_METADATA.GET_DDL('FUNCTION', object_name, OWNER),
'"' || USER || '".',
''
) AS text
FROM dba_objects
WHERE 1=1
--and object_type ='FUNCTION'
and object_type = 'PACKAGE'
--and object_type = 'PROCEDURE'
and OWNER='&O'
/
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE = 'PROCEDURE'
AND OWNER='&O'
;
SELECT DBMS_METADATA.GET_DDL('FUNCTION',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE = 'FUNCTION'
AND OWNER='&O'
;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME, U.OWNER) FROM DBA_OBJECTS U
WHERE U.OBJECT_TYPE = 'PROCEDURE'
AND OWNER='&O'
;
*********************************************************************************
define v_owner='ANUJ'
define v_table_name='TEST'
set feed off term off
select upper('&2') v_table_name from dual;
set feed on term on
var v_owner varchar2(30)
var v_table_name varchar2(30)
begin
:v_owner := '&&v_owner';
:v_table_name := '&&v_table_name';
end;
/
set long 50000 pagesize 300 linesize 300select replace(dbms_metadata.get_ddl(o.OBJECT_TYPE,o.OBJECT_NAME , o.owner),'"','') ddl
from dba_objects o
where 1=1
--and o.owner = :v_owner
and o.OBJECT_NAME = 'LOG'
/
*********************************************************************************
set pagesize 0 long 90000
define N='LLLLL' ---object name
define OWNER='ANUJ1'
select dbms_metadata.get_ddl('FUNCTION','&N','&OWNER') FROM DUAL;
select dbms_metadata.get_ddl('PROCEDURE','&N','&OWNER') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','&N','&OWNER') FROM DUAL;
set pagesize 100 long 90000
********************************
uncomment based on your requirement
set long 200000 pages 0 lines 131 doc off
column txt format a121 word_wrapped
define O='ANUJ1'
define T='EMP'
select DBMS_METADATA.GET_DDL('INDEX',u.index_name,TABLE_OWNER) txt from DBA_INDEXES u where table_name='&T' and OWNER ='&O'
union all
--prompt DDL TABLES
--prompt ******************************
select dbms_metadata.get_ddl('TABLE',TABLE_NAME,OWNER) TABLES from DBA_TABLES where table_name='&T' and OWNER ='&O'
union all
--prompt DDL TB_CONSTRAINTS
--prompt ******************************
select dbms_metadata.get_dependent_ddl('CONSTRAINT',TABLE_NAME,OWNER) TBCONSTRAINTS from dba_constraints where table_name='&T' and OWNER ='&O'
union all
--prompt DDL REF_CONSTRAINTS
--prompt ******************************
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT',TABLE_NAME,OWNER) REFCONSTRAINTS from dba_constraints where table_name='&T' and OWNER ='&O'
union all
--prompt INDEXES
--prompt ******************************
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER(TABLE_NAME),UPPER(OWNER)) from DBA_TABLES where table_name='&T' and OWNER ='&O'
union all
--prompt DDL TRIGGERS
--prompt ******************************
select dbms_metadata.get_dependent_ddl('TRIGGER',TABLE_NAME,OWNER) from dba_triggers where table_name='&T' and OWNER ='&O'
union all
--prompt DDL VIEWS
--prompt ******************************
--select dbms_metadata.get_ddl('VIEW',view_name,OWNER) from dba_views where VIEW_NAME='&T' and OWNER ='&O'
--union all
/* SELECT DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW', master, LOG_OWNER) txt
FROM dba_mview_logs
WHERE LOG_OWNER = UPPER('&O')
and master= UPPER('&T')
union all
*/
SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW_LOG', LOG_TABLE, LOG_OWNER)
FROM dba_mview_logs
WHERE LOG_OWNER = UPPER('&O')
and master= UPPER('&T')
;
set long 1000 pages 100 lines 100
MV logs row count
SET SERVEROUTPUT ON;
DECLARE
CURSOR cur IS
SELECT log_owner, log_table
FROM dba_snapshot_logs
where LOG_OWNER='ANUJ'
order by 2
;
num_rows NUMBER;
cnt NUMBER := 0;
BEGIN
FOR x IN cur LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || x.log_owner || '.' || x.log_table
INTO num_rows;
IF num_rows > 0 THEN
DBMS_OUTPUT.PUT_LINE(x.log_owner || '.' || x.log_table ||
' has ' || num_rows || ' rows');
cnt := cnt + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Cannot access ' || x.log_owner || '.' || x.log_table ||
' -> ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE(cnt || ' materialized view logs have entries');
END;
/
