Метаданные Oracle

Метаданные DATABASE LINK:

SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) || '/' FROM dba_db_links a


Получить метаданные пользователя ANGOR:

set feedback off
set pagesize 25000
set linesize 1000
set long 90000
column script format a1000
set serveroutput on

variable v_username VARCHAR2(30);
exec :v_username := upper('angor');

--This line add a semicolon at the end of each statement
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

-- This will generate the DDL for the user and add his objects,system and role grants
SELECT DBMS_METADATA.GET_DDL('USER',username) as script from DBA_USERS where username= :v_username
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',grantee) as script from DBA_SYS_PRIVS where grantee= :v_username and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',grantee) as script from DBA_ROLE_PRIVS where grantee= :v_username and rownum=1
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',grantee) as script from DBA_TAB_PRIVS where grantee= :v_username and rownum=1;



clear screen
SET LONG 20000
SET LONGCHUNKSIZE 20000
SET PAGESIZE 0
SET LINESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
column script format a1000
set serveroutput on

variable v_username VARCHAR2(30);
exec :v_username := upper('angor');

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('USER', :v_username) as script FROM dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', :v_username) as script from dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', :v_username) as script from dual
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', :v_username) as script from dual;

spool off



set long 20000
set longchunksize 20000
set pagesize 25000
set linesize 1000
set feedback off
set verify off
set trimspool on
column ddl format a1000

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

variable v_username VARCHAR2(30);
exec :v_username := upper('angor');

select dbms_metadata.get_ddl('USER', u.username) AS ddl
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl
from   dba_sys_privs sp
where  sp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
and    rownum = 1
/