Scripts

tbsfree.sql

2005-12-12T12:45:00.000-08:00


column  pct_used     format 999.9       heading "%|Used"
column  name         format a16         heading "Tablespace Name"
column  Kbytes       format 999,999,999 heading "KBytes"
column  used         format 999,999,999 heading "Used"
column  free         format 999,999,999 heading "Free"
column  largest      format 999,999,999 heading "Largest"
column  max_size     format 999,999,999 heading "MaxPoss|Kbytes"
column  pct_max_used format 999.9       heading "%|Max|Used"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
prompt =================================================================
with
free_space_by_tablespace as
     ( select sum(bytes)/1024 Kbytes_free,
                          max(bytes)/1024 largest,
                          tablespace_name
           from  sys.dba_free_space
           group by tablespace_name ),
space_by_permanent_tablespace as
     ( select sum(bytes)/1024 Kbytes_alloc,
                          sum(maxbytes)/1024 Kbytes_max,
                          tablespace_name
           from sys.dba_data_files
           group by tablespace_name ),
space_by_temporary_tablespace as
     ( select sum(bytes)/1024 Kbytes_alloc,
                          sum(maxbytes)/1024 Kbytes_max,
                          tablespace_name
           from sys.dba_temp_files
           group by tablespace_name )
/* Now, finally to the query itself */
select b.tablespace_name name,
           kbytes_alloc kbytes,
           kbytes_alloc-nvl(kbytes_free,0) used,
           nvl(kbytes_free,0) free,
           ((kbytes_alloc-nvl(kbytes_free,0))/
                                                  kbytes_alloc)*100 pct_used,
           nvl(largest,0) largest,
           nvl(kbytes_max,kbytes_alloc) Max_Size,
           decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
  from free_space_by_tablespace a
           RIGHT OUTER JOIN
              space_by_permanent_tablespace b on a.tablespace_name = b.tablespace_name
union all
select b.tablespace_name,
           kbytes_alloc kbytes,
           kbytes_alloc-nvl(kbytes_free,0) used,
           nvl(kbytes_free,0) free,
           ((kbytes_alloc-nvl(kbytes_free,0))/
                                                  kbytes_alloc)*100 pct_used,
           nvl(largest,0) largest,
           nvl(kbytes_max,kbytes_alloc) Max_Size,
           decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
  from free_space_by_tablespace a
           RIGHT OUTER JOIN
              space_by_temporary_tablespace b on a.tablespace_name = b.tablespace_name
/

flat.cmd (Windows)

2005-06-20T10:09:00.001-07:00

Flat.cmd


@echo off

if "%1"=="" goto :usage
if "%2"=="" goto :usage


sqlplus -s %1 @flat.sql %2

goto :done

:Usage

echo "usage             flat un/pw [tables|views]"
echo "example   flat scott/tiger emp dept"
echo "description       Select over standard out all rows of table or view with "
echo "                  columns delimited by tabs."

:done

Flat.sql


set     wrap off
set linesize 100
set     feedback off
set     pagesize 0
set     verify off
set termout off

spool ytmpy.sql


prompt  select
select  lower(column_name)||'||chr(9)||'
from    user_tab_columns
where   table_name = upper('&1') and
    column_id != (select max(column_id) from user_tab_columns where
             table_name = upper('&1'))
order by column_id
/
select  lower(column_name)
from    user_tab_columns
where   table_name = upper('&1') and
    column_id = (select max(column_id) from user_tab_columns where
             table_name = upper('&1'))
                         order by column_id
/
prompt  from    &1
prompt  /

spool off
set termout on
@ytmpy.sql
exit

flat (unix)

2005-06-20T10:09:00.000-07:00


#!/bin/sh 

if [ "$1" = "" ]
then
        cat << EOF

usage:          flat un/pw [tables|views]

example:        flat scott/tiger emp dept

description:    Select over standard out all rows of table or view with 
                columns delimited by tabs.
EOF
        exit
fi


PW=$1
shift

for X in $*
do
sqlplus -s $PW << EOF > /tmp/flat$$.sql
set     wrap off
set     feedback off
set     pagesize 0
set     verify off
prompt  select
select  lower(column_name)||'||chr(9)||'
from    user_tab_columns
where   table_name = upper('$X') and
        column_id != (select max(column_id) from user_tab_columns where
                         table_name = upper('$X'))
order by column_id
/
select  lower(column_name)
from    user_tab_columns
where   table_name = upper('$X') and
        column_id = (select max(column_id) from user_tab_columns where
                         table_name = upper('$X'))
order by column_id
/
prompt  from    $X
prompt  /
prompt  exit
exit
EOF
sqlplus -s $PW << EOF
set     wrap off
set     feedback off
set     pagesize 0
set     verify off
set trimspool on
set linesize 5000
start /tmp/flat$$.sql
exit
EOF
rm /tmp/flat$$.sql
done

dump_csv.sql

2005-06-20T10:06:00.000-07:00


create or replace function  dump_csv( p_query        in varchar2,
                      p_separator in varchar2 default ';',
                      p_dir       in varchar2 ,
                      p_filename  in varchar2 )
  return number
  is
      l_output        utl_file.file_type;
      l_theCursor     integer default dbms_sql.open_cursor;
      l_columnValue   varchar2(2000);
      l_status        integer;
      l_colCnt        number default 0;
      l_separator     varchar2(10) default '';
      l_cnt           number default 0;
  begin
      l_output := utl_file.fopen( p_dir, p_filename, 'w' );

      dbms_sql.parse(  l_theCursor,  p_query,
                                           dbms_sql.native );

      for i in 1 .. 255 loop
          begin
              dbms_sql.define_column( l_theCursor, i,
                                      l_columnValue, 2000 );
              l_colCnt := i;
          exception
              when others then
                  if ( sqlcode = -1007 ) then exit;
                  else
                      raise;
                  end if;
          end;
      end loop;

      dbms_sql.define_column( l_theCursor, 1,
                              l_columnValue, 2000 );

      l_status := dbms_sql.execute(l_theCursor);

      loop
          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
          l_separator := '';
          for i in 1 .. l_colCnt loop
              dbms_sql.column_value( l_theCursor, i,
                                     l_columnValue );
              utl_file.put( l_output,
                            l_separator || l_columnValue );
              l_separator := p_separator;
          end loop;
          utl_file.new_line( l_output );
          l_cnt := l_cnt+1;
      end loop;
      dbms_sql.close_cursor(l_theCursor);

      utl_file.fclose( l_output );
      return l_cnt;
  end dump_csv;
/

df_space.sql (need update)

2005-06-20T10:05:00.000-07:00


select substr(file_name, 1, 40) file_name
     , substr(tablespace_name, 1, 20) tablespace
     , bytes / 1024 / 1024 Meg
     , blocks
     , autoextensible
     , maxbytes
     , increment_by
  from dba_data_files
/

debug.sql

2005-06-20T09:59:00.001-07:00


set echo off

create or replace
type Argv as
table of varchar2(4000);
/

create table debugTab(
  userid     varchar2(30) primary key,
  modules     varchar2(4000),
  locat         varchar2(4000),
  filename varchar2(4000)
)
/

create or replace
trigger bi_fer_debugtab
before insert on debugtab for each row
begin
  :new.modules := upper( :new.modules );
end;
/

create or replace
package debug as
--
--  version 1.0
--    clbeck - 13-OCT-98 - Initial version
--
--  PACKAGE TO DUMP DEBUG INFORMATION OF PL/SQL ROUTINE
--  TO A FILE DURING EXECUTION
--
--
--  This package allows the developer to selectively produce debug
--    iformation for pl/sql process.
--  
--  Setup:
--    Make sure the utl_file_dir paramter is assigned in the init.ora
--    file.  You need an entry for each dir that you want to be able to
--    write to.
--    eg.
--      utl_file_dir = /tmp
--      utl_file_dir = /home/clbeck/sql/debug
--
--  Usage:
--    There are two procedure to write debug information ( f and fa ).
--    Anywhere in your code that you want to print debug information use:
--
--      debug.f( 'Expected %s bytes, got %s bytes', l_expect, l_got  );
--  
--    This will replace the the first %s with the value of l_expect and the 
--    second %s with the value of l_got.
--
--    If you have more than 10 %s in your string then you will need to use the fa
--    procedure like:
--
--      debug.fa( 'List: %s,%s,%s,%s,%s,%s', 
--                  argv( 1, 2, l_num, 'Chris', l_cnt, 10 ) );
--
--  Runtime:
--    To enable the debug run:
--
--      debug.init( 'myProc' );
--
--    This will cause only debug for the procedure/package named 
--    myProc to be generated.
--    All other debug statements will generate no output.  
--    To debug all procedures/packages,
--    set p_modules = 'ALL';
--
--    To stop debug run:
--
--      debug.clear;
--
--  Output:
--    The output looks like:
--
--      981013 130530 (CLBECK.MYPROC, 221)  this is my debug output 
--      ^^^^^^ ^^^^^^  ^^^^^^^^^^^^^  ^^^   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--      date   time    owner.proc  lineno   message
--
--
-- Enhancements and Bugs:
--
--  Send all enhancements requests and bugs to me, 
--  Christopher Beck (clbeck@us.oracle.com)
--
--

  g_dir_locat constant varchar2(4000) := '/tmp';

  emptyDebugArgv Argv;

  --
  --  Initializes the debuging for specified p_modules and will dump the
  --  output to the p_dir directory on the server for the user p_user.
  --
  procedure init(
    p_modules in varchar2 default 'ALL',
    p_dir     in varchar2 default g_dir_locat,
    p_file    in varchar2 default null,
    p_user    in varchar2 default user );

  procedure f(
    p_message in varchar2,
    p_arg1    in varchar2 default null,
    p_arg2    in varchar2 default null,
    p_arg3    in varchar2 default null,
    p_arg4    in varchar2 default null,
    p_arg5    in varchar2 default null,
    p_arg6    in varchar2 default null,
    p_arg7    in varchar2 default null,
    p_arg8    in varchar2 default null,
    p_arg9    in varchar2 default null,
    p_arg10   in varchar2 default null );

  procedure fa(
    p_message in varchar2,
    p_args    in Argv default emptyDebugArgv );

  procedure clear(
    p_user in varchar2 default user );

  --
  -- Returns the current status of debugging for the user p_user.
  --
  procedure status(
    p_user    in  varchar2 default user,
    p_modules out varchar2,
    p_file    out varchar2,
    p_dir     out varchar2 );

end debug;
/

show error

grant execute on debug to public
/

create or replace
package body debug as

  g_owner varchar2(2000);
  g_name varchar2(2000);
  g_lineno number;
  g_caller_t varchar2(2000);

  g_file varchar2(2000);

  procedure init(
    p_modules in varchar2 default 'all',
    p_dir     in varchar2 default g_dir_locat,
    p_file    in varchar2 default null,
    p_user    in varchar2 default user ) is
  --
    r debugTab%rowtype;
  begin
    clear( p_user );
    insert into debugTab values ( p_user, p_modules, p_dir, p_file );
  end init;


  procedure clear( p_user varchar2 default user ) is
  begin
    delete from debugTab where userid = p_user;
  end clear;


  procedure status(
    p_user    in  varchar2 default user,
    p_modules out varchar2,
    p_file     out varchar2,
    p_dir    out varchar2 ) is
  begin
    select modules, locat, filename
      into p_modules, p_dir, p_file
      from debugTab
     where userid = p_user;
  exception
    when NO_DATA_FOUND then
      p_modules := null;
      p_dir := null;
      p_file := null;
  end status;

  procedure who_called_me(
    owner      out varchar2,
    name       out varchar2,
    lineno     out number,
    caller_t   out varchar2 ) is
  --
    call_stack  varchar2(4096) default dbms_utility.format_call_stack;
    n           number;
    found_stack BOOLEAN default FALSE;
    line        varchar2(255);
    cnt         number := 0;
  begin
    loop
      n := instr( call_stack, chr(10) );
      exit when ( cnt = 3 or n is NULL or n = 0 );
  --
      line := substr( call_stack, 1, n-1 );
      call_stack := substr( call_stack, n+1 );
  --
      if not found_stack then
        if line like '%handle%number%name%' then
          found_stack := TRUE;
        end if;
      else
        cnt := cnt + 1;
        -- cnt = 1 is ME
        -- cnt = 2 is MY Caller
        -- cnt = 3 is Their Caller
        if ( cnt = 3 ) then
          lineno := to_number(substr( line, 13, 6 ));
          line   := substr( line, 21 );
          if ( line like 'pr%' ) then
            n := length( 'procedure ' );
          elsif ( line like 'fun%' ) then
            n := length( 'function ' );
          elsif ( line like 'package body%' ) then
            n := length( 'package body ' );
          elsif ( line like 'pack%' ) then
            n := length( 'package ' );
          else
            n := length( 'anonymous block ' );
          end if;
          caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
          line := substr( line, n );
          n := instr( line, '.' );
          owner := ltrim(rtrim(substr( line, 1, n-1 )));
          name  := ltrim(rtrim(substr( line, n+1 )));
        end if;
      end if;
    end loop;
  end who_called_me;

  function is_number( n varchar2 ) return boolean is
  begin
    if n between '0' and '9' then
      return true;
    end if;
    return false;
  end is_number;

  function parse_it(
    p_message in varchar2,
    p_args in argv default emptyDebugArgv ) return varchar2 is
  --
    l_tmp long := p_message;
    l_str long := null;
    l_idx number;

    l_numstr1 varchar2(10);
    l_numstr2 varchar2(10);

    l_tmp1 long;
    l_str1 long;

    l_num number;
    l_char long;
  begin

    for i in 1 .. p_args.count loop
      l_idx := instr( l_tmp, '%' ) ;
      exit when nvl(l_idx,0) = 0;

      l_str := l_str || substr( l_tmp, 1, l_idx-1 );
      l_tmp := substr( l_tmp, l_idx+1 );

      if substr( l_tmp, 1, 1 ) = 's' or
         substr( l_tmp, 1, 1 ) = 'd' then
        l_str := l_str || p_args(i);
        l_tmp := substr( l_tmp, 2 );
      elsif is_number( substr( l_tmp, 1, 1 ) ) or
            substr( l_tmp, 1, 1 ) = '.' then

        l_numstr1 := null;
        l_numstr2 := null;

        l_tmp1 := l_tmp;
        l_str1 := l_str;

        loop
          exit when not is_number( substr( l_tmp1, 1, 1 ) );
          l_numstr1 := l_numstr1 || substr( l_tmp1, 1, 1 );
          l_tmp1 := substr( l_tmp1, 2 );
        end loop;

        if substr( l_tmp1, 1, 1 ) = '.' then
          l_tmp1 := substr( l_tmp1, 2 );
          if is_number( substr( l_tmp1, 1, 1 ) ) then
            loop
              exit when not is_number( substr( l_tmp1, 1, 1 ) );
              l_numstr2 := l_numstr2 || substr( l_tmp1, 1, 1 );
              l_tmp1 := substr( l_tmp1, 2 );
            end loop;
          else
            l_tmp1 := '!' || l_tmp1;
          end if;
        end if;

        begin
          if substr( l_tmp1, 1, 1 ) = 's' then
            l_tmp := substr( l_tmp1, 2 );
            if l_numstr2 is null then
              l_tmp1 := p_args(i);
            else
              l_tmp1 := substr( p_args(i), 1, l_numstr2 );
            end if;
            if l_numstr1 is not null then
              l_tmp1 := lpad( l_tmp1, l_numstr1 );
            end if;
            l_str := l_str1 || l_tmp1;
          elsif substr( l_tmp1, 1, 1 ) = 'd' then
            l_tmp := substr( l_tmp1, 2 );
            if l_numstr1 is null then
              l_tmp1 := lpad( '9', 39, '9' );
            else
              l_tmp1 := lpad( '9', l_numstr1, '9' );
            end if;
            if l_numstr2 is not null then
              l_tmp1 := substr( l_tmp1, 1, l_numstr1-l_numstr2 ) || '.' || 
                                                substr( l_tmp1, -l_numstr2 );
            end if;
            l_str := l_str1 || to_char( to_number( p_args(i) ), l_tmp1 );
          else
            l_str := l_str || '%';
          end if;
        exception
          when others then
            l_str := l_str1 || 'XXXXXXXXXX';
        end;

      else
        l_str := l_str || '%';
      end if;

    end loop;

    return l_str || l_tmp;

  exception
    when others then
      return l_str || l_tmp;
  end parse_it;


  procedure internal_f(
    p_message in varchar2,
    p_args    in Argv default emptyDebugArgv ) is
  --
    l_locat varchar2(4000);
    l_modules varchar2(4000);
    l_filename varchar2(4000);
    l_message long := null;
    l_file utl_file.file_type;
    l_date varchar2(255);
  begin

    select modules, locat, filename, to_char( sysdate, 'YYMMDD HH24MISS' )
      into l_modules, l_locat, l_filename, l_date
      from debugTab
     where userid = user;

    if instr( l_modules, nvl(g_name,'BLAH') ) = 0 and l_modules <> 'ALL' then
      return;
    end if;

    if l_filename is not null then
      g_file := l_filename;
    end if;

    l_message := parse_it( p_message, p_args );
/*
    l_message := p_message;

    begin
      for i in 1 .. p_args.count loop
        if instr( l_message, '%s' ) = 0 then
          exit;
        else
          l_message := substr( l_message, 1, instr( l_message, '%s' )-1 ) ||
                       p_args(i) || 
                       substr( l_message, instr( l_message, '%s' )+2 );
        end if;
      end loop;
    exception
      when others then
        null;
    end;
*/

    l_message := replace( l_message, '\n', chr(10) );
    l_message := replace( l_message, '\t', chr(9) );

    l_file := utl_file.fopen( l_locat, g_file, 'a', 32767 );
    if not utl_file.is_open( l_file ) then
      dbms_output.put_line( 'File not opened' );
    end if;
    if g_owner is null then
      g_owner := user;
      g_name := 'ANONYMOUS BLOCK';
    end if;
    utl_file.put( l_file, '' );
    utl_file.put_line( l_file,
                       l_date ||
                       ' (' || lpad( g_owner || '.' || g_name, 20 ) || ',' ||
                       lpad(g_lineno,4) || ') ' || l_message );
    utl_file.fclose( l_file );

  exception
    when NO_DATA_FOUND then
      -- dbms_output.put_line( sqlerrm );
      null;
  end internal_f;


  procedure fa(
    p_message in varchar2,
    p_args    in Argv default emptyDebugArgv ) is
  begin
    who_called_me( g_owner, g_name, g_lineno, g_caller_t );
    internal_f( p_message, p_args );
  end fa;


  procedure f(
    p_message in varchar2,
    p_arg1    in varchar2 default null,
    p_arg2    in varchar2 default null,
    p_arg3    in varchar2 default null,
    p_arg4    in varchar2 default null,
    p_arg5    in varchar2 default null,
    p_arg6    in varchar2 default null,
    p_arg7    in varchar2 default null,
    p_arg8    in varchar2 default null,
    p_arg9    in varchar2 default null,
    p_arg10   in varchar2 default null ) is
  begin
    who_called_me( g_owner, g_name, g_lineno, g_caller_t );
    internal_f( p_message, 
                argv( substr( p_arg1, 1, 4000 ),
                      substr( p_arg2, 1, 4000 ),
                      substr( p_arg3, 1, 4000 ),
                      substr( p_arg4, 1, 4000 ),
                      substr( p_arg5, 1, 4000 ),
                      substr( p_arg6, 1, 4000 ),
                      substr( p_arg7, 1, 4000 ),
                      substr( p_arg8, 1, 4000 ),
                      substr( p_arg9, 1, 4000 ),
                      substr( p_arg10, 1, 4000 ) ) );
  end f;

begin

  g_file := 'DEBUGF_'||userenv('SESSIONID');

end debug;
/

show error

dba_procedures.sql

2005-06-20T09:59:00.000-07:00


set echo on
create or replace view dba_procedures
as
select owner,
       object_name name,
       object_id,
       object_type,
       created,
       last_ddl_time,
       status,
       (select decode( bitand(options,16), 16, 'INVOKER', 'DEFINER' )
          from procedure$
         where obj# = dba_objects.object_id ) auth_id
  from dba_objects
 where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY' )
/

create public synonym dba_procedures for sys.dba_procedures
/

set echo off

cria_user.sql

2005-06-20T09:58:00.000-07:00


set echo on

create user &&1 identified by &&1
 default tablespace &&2
 temporary tablespace &3
 quota unlimited on &&2
/
grant connect, resource to &&1
/
revoke unlimited tablespace from &&1
/
set echo off

cons.sql

2005-06-20T09:57:00.000-07:00


column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
         b.r_constraint_name parent_cons_name,
         max(decode(position, 1,     '"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 2,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 3,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 4,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 5,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 6,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 7,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 8,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 9,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,10,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,11,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,12,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,13,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,14,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,15,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,16,', '||'"'||
                substr(column_name,1,30)||'"',NULL))
            child_columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
         max(decode(position, 1,     '"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 2,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 3,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 4,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 5,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 6,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 7,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 8,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position, 9,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,10,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,11,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,12,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,13,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,14,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,15,', '||'"'||
                substr(column_name,1,30)||'"',NULL)) ||
         max(decode(position,16,', '||'"'||
                substr(column_name,1,30)||'"',NULL))
            parent_columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type in ( 'P', 'U' )
   group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
  and parent.parent_tname = upper('&1')
/

calendario.sql

2005-06-20T09:56:00.000-07:00


select to_char(dt,'yy') ano,
       to_char(dt,'mm') Mes,
       to_char(dt,'iw') Semana,
             max(decode( to_char(dt,'d'), 2, to_char(dt,'dd'), null )) "2a",
             max(decode( to_char(dt,'d'), 3, to_char(dt,'dd'), null )) "3a",
             max(decode( to_char(dt,'d'), 4, to_char(dt,'dd'), null )) "4a",
             max(decode( to_char(dt,'d'), 5, to_char(dt,'dd'), null )) "5a",
             max(decode( to_char(dt,'d'), 6, to_char(dt,'dd'), null )) "6a",
             max(decode( to_char(dt,'d'), 7, to_char(dt,'dd'), null )) "Sa",
             max(decode( to_char(dt,'d'), 1, to_char(dt,'dd'), null )) "Do"
  from ( select trunc(sysdate, 'yyyy')+rownum-1  dt
                   from all_objects
                  where rownum <= 366
           )
 group by to_char(dt,'yy') ,
       to_char(dt,'mm') ,
       to_char(dt,'iw') 
 order by 1,2,3
/

audit_new_old.sql

2005-06-20T09:53:00.000-07:00


drop table audit_tbl
/
create table audit_tbl
(    timestamp    date,
    who            varchar2(30),
    tname        varchar2(30),
    cname        varchar2(30),
    old            varchar2(2000),
    new            varchar2(2000)
)
/

create or replace package audit_pkg
as
    procedure check_val( l_tname in varchar2, 
                             l_cname in varchar2, 
                 l_new in varchar2, 
                             l_old in varchar2 );

    procedure check_val( l_tname in varchar2, 
                             l_cname in varchar2, 
                     l_new in date, 
                             l_old in date );

    procedure check_val( l_tname in varchar2, 
                             l_cname in varchar2, 
                 l_new in number, 
                             l_old in number );
end;
/


create or replace package body audit_pkg
as

procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
             l_new in varchar2,
                     l_old in varchar2 )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
                             l_old, l_new );
    end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
             l_new in date, l_old in date )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
          to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
    end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
             l_new in number, l_old in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, sys_context('USERENV','OS_USER'), upper(l_tname), upper(l_cname),
                                 l_old, l_new );
    end if;
end;

end audit_pkg;
/

audit_dml.sql

2005-06-20T09:52:00.000-07:00


set echo on

drop table audit_dml
/
create table audit_dml
(   timestamp           date,
    who                 varchar2(30),
    op                  varchar2(6),
    seq                 number(6),
    tname               varchar2(30),
    cname               varchar2(30),
    old                 varchar2(2000),
    new                 varchar2(2000),
    rid_old             rowid,
    rid_new             rowid,
    data_type           char(1) 
)
/

drop sequence x#_seq;

create sequence x#_seq;

create or replace package audit_pkg
as
    procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in varchar2, 
        l_old in varchar2,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number
                        );

    procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in date, 
        l_old in date,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number
                        );

    procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in number, 
        l_old in number,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number
                        );
end;
/


create or replace package body audit_pkg
as
procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in varchar2, 
        l_old in varchar2,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_dml values
        ( sysdate, sys_context('USERENV','OS_USER'), l_op, l_seq, upper(l_tname), upper(l_cname),
                             l_old, l_new, l_rid_old, l_rid_new, 'C' );
    end if;
end;

procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in date, 
        l_old in date,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_dml values
        ( sysdate, sys_context('USERENV','OS_USER'), l_op,l_seq,  upper(l_tname), upper(l_cname),
          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
          to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ), l_rid_old, l_rid_new , 'D' );
    end if;
end;

procedure check_val( 
        l_tname in varchar2, 
        l_cname in varchar2, 
        l_new in number, 
        l_old in number,
        l_rid_new in rowid, 
        l_rid_old in rowid,
        l_op in varchar2,
        l_seq in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_dml values
        ( sysdate, sys_context('USERENV','OS_USER'), l_op, l_seq, upper(l_tname), upper(l_cname),
                                 l_old, l_new, l_rid_old, l_rid_new , 'N' );
    end if;
end;

end audit_pkg;
/
set echo off

audit_ddl.sql

2005-06-20T09:51:00.000-07:00


set echo on

alter trigger auditddl_trg disable
/

drop table audit_ddl
/

create table audit_ddl (
        timestamp       date default sysdate,
        owner           varchar2(20) default user,
        osuser          varchar2(20),
        object          varchar2(30),
        event           varchar2(30),
        type_obj        varchar2(30))
/

create or replace trigger auditddl_trg 
        after truncate or drop or create or comment or grant or revoke on schema
begin
        insert into audit_ddl (osuser, object, event, type_obj ) 
           values (sys_context('USERENV','OS_USER'), ora_dict_obj_name, ora_sysevent, ora_dict_obj_type);
end;
/
set echo off

allusers.sql

2005-06-20T09:45:00.000-07:00


column temporary_tablespace format a20
column default_tablespace   format a20

select username, created, default_tablespace, temporary_tablespace, account_status
  from dba_users
 order by account_status, created
/

allsyn.sql

2005-06-20T09:43:00.000-07:00


set echo on

select owner, synonym_name, table_owner, table_name from all_synonyms
 where table_owner = upper('&1')
/

set termout off
undefine 1
set termout on
set echo off

dbls.sql

2005-06-02T21:40:00.000-07:00


column object_name format a30
column tablespace_name format a30
column object_type format a12
column status format a1

break on object_type skip 1

select object_type, object_name,
       decode( status, 'INVALID', '*', '' ) status,
       decode( object_type,
                'TABLE', (select tablespace_name from user_tables where 
table_name = object_name),
                'INDEX', (select tablespace_name from user_indexes where 
index_name = object_name),
                null ) tablespace_name
from user_objects a
where object_name like upper('%&1%')
order by object_type, object_name
/
clear break
column status off

connect.sql

2005-06-02T21:22:00.000-07:00


set termout off
save afiedt.buf replace
connect &1
@login
get afiedt.buf nolist

login.sql

2005-05-31T19:59:00.000-07:00


define _editor=vi
-- Used by Trusted Oracle
column ROWLABEL format A15
-- Used for the SHOW ERRORS command
column LINE/COL format A8
column ERROR    format A65  WORD_WRAPPED
-- Used for the SHOW SGA command
column name_col_plus_show_sga format a24
-- Defaults for SHOW PARAMETERS
column name_col_plus_show_param format a36 heading NAME
column value_col_plus_show_param format a30 heading VALUE
-- For backward compatibility

set pagesize 9999
set linesize 131
set editfile "/tmp/afiedt.buf"
set numwidth 13
set verify off
set termout off
set autoprint on
set sqlblanklines on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set nls_numeric_characters=',.';
alter session set optimizer_mode = choose;

column tree format a40

-- Defaults for SET AUTOTRACE EXPLAIN report
column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a200
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44

set serveroutput on size 1000000 format wrapped
set trimspool on
set long 10000
set arraysize 100

column plan_plus_exp format a80

define gname=idle
column global_name new_value gname
select lower(user) || '@' || 
       substr(global_name, 1, decode( dot, 0, 
              length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on
set feedback on