HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Data dictionary based DDL skeletons
ORACLE STUFFS
01-Aug-2017
For building usable structure for copies, bulk hard operations, etc. I have collected few seletons to be enhanced to get useful code when needed.
These codes are neither complete nor universal, they just reflect what I needed at the point of time I wrote them. They can just be useful not to start from scratch.


Table and partitions
Script for tables and partitions omiting indexes, costraints and defaults, all compressed.

Declare
  c_TableName Varchar2(30 CHAR):='&1';
  ------
  v_STMT      CLOB;
  v_delim     Varchar2(10 CHAR);
  v_delim2    Varchar2(10 CHAR);
  v_keycols   Varchar2(4000 CHAR);
  v_hival     Varchar2(4000 CHAR);
Begin
  for r1 in (
    Select t.TABLE_NAME,tp.PARTITIONING_TYPE, tp.SUBPARTITIONING_TYPE 
    from USER_TABLES t
    left join USER_PART_TABLES tp on tp.TABLE_NAME=t.TABLE_NAME
    where t.TABLE_NAME=c_TableName
  )LOOP
    v_stmt:='Create table '||r1.TABLE_NAME||'(';  
    v_delim:='   ';
    for r2 in (
       Select c.COLUMN_NAME
             ,c.DATA_TYPE||
                case 
                    when c.DATA_TYPE like '%CHAR%' and c.CHAR_USED='C' then '('||to_char(c.CHAR_LENGTH)||' CHAR)'
                    when c.DATA_TYPE like '%CHAR%' then '('||to_char(c.DATA_LENGTH)||' BYTE)'
                    when c.DATA_TYPE = 'NUMBER' and c.DATA_PRECISION is not null then '('||to_char(c.DATA_PRECISION)||','||to_char(c.DATA_SCALE)||')'
                    when c.DATA_TYPE = 'NUMBER' and c.DATA_SCALE is not null then '(*,'||to_char(c.DATA_SCALE)||')'
                end as DATA_TYPE  
       from USER_TAB_COLUMNS c 
       where c.TABLE_NAME=r1.TABLE_NAME
       order by c.COLUMN_ID
    )LOOP
      v_stmt:=v_stmt||chr(10)||v_delim||'"'||rpad(r2.COLUMN_NAME||'"',31)||' '||r2.DATA_TYPE;
      v_delim:='  ,';
    end LOOP; --r2
    v_stmt:=v_stmt||chr(10)||')';
    if r1.PARTITIONING_TYPE!='NONE' then 
      Select listagg(kc.column_name,',')within group(order by kc.COLUMN_POSITION)into v_keycols from USER_PART_KEY_COLUMNS kc where kc.NAME=r1.TABLE_NAME;
      v_stmt:=v_stmt||chr(10)||'partition by '||r1.PARTITIONING_TYPE||'('||v_keycols||')';
      if r1.SUBPARTITIONING_TYPE!='NONE' then 
        Select listagg(kc.column_name,',')within group(order by kc.COLUMN_POSITION)into v_keycols from USER_SUBPART_KEY_COLUMNS kc where kc.NAME=r1.TABLE_NAME;
        v_stmt:=v_stmt||chr(10)||'subpartition by '||r1.SUBPARTITIONING_TYPE||'('||v_keycols||')';
      end if;
      v_stmt:=v_stmt||chr(10)||'(';
      v_delim:='  ';
      for r2 in(
        Select utp.PARTITION_NAME 
        from USER_TAB_PARTITIONS utp
        where utp.TABLE_NAME=r1.TABLE_NAME
        order by utp.partition_position
      )LOOP
        Select utp.high_value into v_hival from USER_TAB_PARTITIONS utp where utp.TABLE_NAME=r1.TABLE_NAME and utp.PARTITION_NAME=r2.PARTITION_NAME;
        v_stmt:=v_stmt||chr(10)||v_delim||'partition '||r2.PARTITION_NAME||' values '||case when r1.PARTITIONING_TYPE='RANGE' then 'less than ' end||'('||v_hival||') compress';
        v_delim:=' ,';
        if r1.SUBPARTITIONING_TYPE!='NONE' then 
          v_stmt:=v_stmt||chr(10)||'(';
          v_delim2:='  ';
          for r3 in(
            Select utp.SUBPARTITION_NAME 
            from USER_TAB_SUBPARTITIONS utp
            where utp.TABLE_NAME=r1.TABLE_NAME
             and utp.PARTITION_NAME=r2.PARTITION_NAME
            order by utp.subpartition_position
          )LOOP
            Select utp.high_value into v_hival from USER_TAB_SUBPARTITIONS utp where utp.TABLE_NAME=r1.TABLE_NAME and utp.PARTITION_NAME=r2.PARTITION_NAME and utp.SUBPARTITION_NAME=r3.SUBPARTITION_NAME;
            v_stmt:=v_stmt||chr(10)||v_delim2||'subpartition '||r3.SUBPARTITION_NAME||' values '||case when r1.SUBPARTITIONING_TYPE='RANGE' then 'less than ' end||'('||v_hival||') compress';
            v_delim2:=' ,';
          end LOOP;
          v_stmt:=v_stmt||chr(10)||')';
        end if;
      end loop;
      v_stmt:=v_stmt||chr(10)||')';
    end if;
    v_stmt:=v_stmt||' compress';
      --------------------------------------------------------
        DBMS_OUTPUT.PUT_LINE(v_stmt);                       --
      --------------------------------------------------------
  end LOOP; --r1
End;

[Download]


Indexes
Script for indexes

Declare
  c_TableName Varchar2(100 CHAR):='&1';
  ---------------------------------
  v_EXPR Varchar2(4000 CHAR);
  v_EXP1 Varchar2(4000 CHAR);
Begin
  for r1 in(
    with COLS as(
      Select IXC.INDEX_NAME
        ,listagg(IXC.COLUMN_NAME||case when IXC.DESCEND='DESC' then ' desc' end,',')within group(order by IXC.COLUMN_POSITION) as CCC
        ,count(1) as CNT
      from USER_IND_COLUMNS IXC 
      group by IXC.INDEX_NAME
    )
    Select 
      'Create'||case when IX.UNIQUENESS='UNIQUE' then ' unique' when IX.INDEX_TYPE='BITMAP' then ' bitmap' end||' index '||IX.INDEX_NAME||' on '||IX.TABLE_NAME||'('||case when IX.INDEX_TYPE='FUNCTION-BASED NORMAL' then '###' else COLS.CCC end||')'||case when IX.PARTITIONED='YES' then ' local' end as STMT
     ,IX.TABLE_NAME,IX.INDEX_NAME,IX.INDEX_TYPE
    from USER_INDEXES IX 
    left join COLS on COLS.INDEX_NAME=IX.INDEX_NAME
    where IX.INDEX_TYPE in ('NORMAL','FUNCTION-BASED NORMAL','BITMAP')
      and IX.TABLE_NAME=c_Tablename
    order by IX.TABLE_NAME
  )LOOP
    if r1.INDEX_TYPE='FUNCTION-BASED NORMAL' then
      v_EXPR:='';
      for r2 in (Select IXE.COLUMN_POSITION from USER_IND_EXPRESSIONS IXE where IXE.INDEX_NAME=r1.INDEX_NAME order by 1) LOOP
        Select IXE.COLUMN_EXPRESSION into v_EXP1
        from USER_IND_EXPRESSIONS IXE
        where IXE.INDEX_NAME=r1.INDEX_NAME and IXE.COLUMN_POSITION=r2.COLUMN_POSITION;
        v_EXPR:=v_EXPR||','||v_EXP1;
      end LOOP;  
      r1.STMT:=replace(r1.STMT,'###',ltrim(v_EXPR,','));
    end if;
    ----------------------------------------------
      DBMS_OUTPUT.put_line(r1.STMT);            --
    ----------------------------------------------
  End LOOP;
End;

[Download]


Views
Script for views (the simplest).

Declare
  c_viewRegexpMask Varchar2(500 CHAR):='&1';
  v_text Varchar2(32767 BYTE);
Begin
  for r1 in (select VIEW_NAME,TEXT_VC,TEXT_LENGTH from USER_VIEWS v where regexp_like(v.VIEW_NAME,c_viewRegexpMask) )LOOP
    if r1.TEXT_LENGTH<=2000 then 
      v_text:=r1.TEXT_VC;
    else
      Select v.TEXT into v_text from USER_VIEWS v where v.VIEW_NAME=r1.VIEW_NAME; 
    end if;
    v_text:='Create or replace view '||r1.VIEW_NAME||' as '||v_text;
    ----------------------------------------------
      DBMS_OUTPUT.put_line(v_text);            --
    ----------------------------------------------
  end LOOP;
End;

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky