set feedback off
set heading  off
set pagesize 0
set trimspool on
set pages 0
set linesize 32000
column VDDL format a32000 word_wrapped

select 'drop table sperrorlog purge;'
from dual;

select 'set errorlogging on'
from dual;


variable v_user varchar2(30);
begin
  :v_user:='EMUIA';
end;
/

select case when rownum=1 then 
          'create user ' || u.name || ' identified by values ''' || u.spare4 || ''' default tablespace ' ||  u1.default_tablespace || ' quota unlimited on ' || nvl(q.tablespace_name, u1.default_tablespace) || ';'
       else
          'alter user ' || u.name || ' quota unlimited on ' || nvl(q.tablespace_name, u1.default_tablespace) || ';'
       end as vsql
from sys.user$ u, dba_ts_quotas q, dba_users u1
where u.name=:v_user
and   u.name=q.username(+)
and    u.name=u1.username;

select case when grantable='YES' then 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || ' WITH GRANT OPTION ;'
        ELSE 'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || ';'
        END as VSQL
from dba_tab_privs
where grantee=trim(upper(:v_user));


select case when admin_option='YES' then  'GRANT ' || privilege || ' TO ' || grantee || ' WITH ADMIN OPTION;'
    ELSE 'GRANT ' || privilege || ' TO ' || grantee || ';'
    END as VSQL
from dba_sys_privs
where grantee=trim(upper(:v_user));

select 'create role ' || granted_role || ';' as VSQL
from dba_role_privs
where grantee=trim(upper(:v_user));

select case when tp.grantable='YES' then 'GRANT ' || privilege || ' ON ' || tp.owner || '.' || tp.table_name || ' TO ' || tp.grantee || ' WITH GRANT OPTION;'
      ELSE 'GRANT ' || privilege || ' ON ' || tp.owner || '.' || tp.table_name || ' TO ' || tp.grantee || ';'
      END as VSQL
from dba_tab_privs tp, dba_role_privs rp
where rp.grantee=trim(upper(:v_user))
and   tp.grantee=rp.granted_role
and   tp.grantee not in ('SELECT_CATALOG_ROLE');


select case when sp.admin_option='YES' then  'GRANT ' || sp.privilege || ' TO ' || sp.grantee || ' WITH ADMIN OPTION;'
    ELSE 'GRANT ' || sp.privilege || ' TO ' || sp.grantee || ';'
    END as VSQL
from dba_sys_privs sp, dba_role_privs rp
where rp.grantee=trim(upper(:v_user))
and   sp.grantee= rp.granted_role;

select case when rp.admin_option='YES' then  'GRANT ' || rp.granted_role || ' TO ' || rp.grantee || ' WITH ADMIN OPTION;'
      ELSE 'GRANT ' || rp.granted_role || ' TO ' || rp.grantee || ';'
      END as VSQL
from dba_role_privs rp
where rp.grantee=trim(upper(:v_user));

variable P_OWNER varchar2(30);

begin
  :P_OWNER:=:v_user;
end;
/

select 'alter session set current_schema=' || :P_OWNER || ';'
from dual;

begin
  dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_Transform,
                                      'STORAGE',false);
                                      
  dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_Transform,
                                      'SQLTERMINATOR',true);
  
  dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_Transform,
                                      'SEGMENT_ATTRIBUTES',false); 
                                      
  dbms_metadata.SET_TRANSFORM_PARAM(dbms_metadata.session_Transform,
                                      'REF_CONSTRAINTS',false);
end;
/

set long 200000000

select dbms_metadata.get_ddl('TABLE', o.table_name, owner) as VDDL
from all_tables o
where owner=:P_OWNER
and table_name not like 'MDRT%'
union all
select dbms_metadata.get_ddl('INDEX', index_name, owner) as VDDL
from all_indexes o
where owner=:P_OWNER
and UNIQUENESS!='UNIQUE'
and ityp_name!='SPATIAL_INDEX'
union all
select dbms_metadata.get_ddl(replace(o.object_Type,' ','_'), o.object_name, owner) as VDDL
from all_objects o
where owner=:P_OWNER
and object_type!='TABLE'
and object_type!='INDEX'
and object_type not like '%PARTITION'
and object_type!='LOB'
and object_name not like 'MDRS\_%$' escape '\'
;

with v_all_constraints as (
select owner, table_name, constraint_name, r_constraint_name, r_table_name,
       constraint_type, status
from all_constraints
where owner=:P_OWNER
and constraint_Type!='C'
model
dimension by (constraint_type, constraint_name, r_constraint_name)
measures (table_name, cast(null as varchar2(30)) as r_table_name, owner, status)
rules
(
  r_table_name[constraint_type='R',any, any]=table_name[constraint_type='P',cv(r_constraint_name),null]
)
)
select 'alter table ' || c.owner || '.' || c.table_name ||
       ' add constraint ' || c.constraint_name || ' foreign key (' ||
       listagg(column_name,',') within group (order by null) || ')
       references ' || c.owner ||'.' || r_table_name || ' ' || regexp_replace(status,'D$',';')
from v_all_constraints c, all_cons_columns col
where c.owner=:P_OWNER
and   c.constraint_type='R'
and   c.owner=col.owner
and   c.table_name=col.table_name
and   c.constraint_name=col.constraint_name
group by c.owner, c.table_name, c.constraint_name, r_table_name, status
;