Parsing delimited string for IN operator in SQL

SQL> create or replace type myTableType as table of number;
    /
Type created.

SQL> create or replace function str2tbl( p_str in varchar2 ) return myTableType
   as
       l_str   long default p_str || ',';
       l_n        number;
       l_data    myTableType := myTabletype();
   begin
       loop
           l_n := instr( l_str, ',' );
           exit when (nvl(l_n,0) = 0);
           l_data.extend;
           l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
           l_str := substr( l_str, l_n+1 );
       end loop;
       return l_data;
   end;
   /

Function created.

SQL>
SQL> select * from all_users
   where user_id in ( select *
     from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
   /

Space Information In a Database

select nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       trunc(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,2) pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name

Generate dummy data in Oracle table (Courtesy asktom.oracle.com)

create or replace procedure do_dummy_data( p_tname in varchar2,
p_records in number )
 authid current_user
 as
     l_insert long;
     l_rows   number default 0;
 begin

    execute immediate 'create table clone_' || p_tname ||
                      ' as select * from ' || p_tname ||
                      ' where 1=0';

     l_insert := 'insert into clone_' || p_tname ||
                 ' select ';

     for x in ( select data_type, data_length,
                 rpad( '9',data_precision,'9')/power(10,data_scale) maxval
                  from user_tab_columns
                 where table_name = 'CLONE_' || upper(p_tname)
                 order by column_id )
     loop
         if ( x.data_type in ('NUMBER', 'FLOAT' ))
         then
             l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
         elsif ( x.data_type = 'DATE' )
         then
             l_insert := l_insert ||
                   'sysdate+dbms_random.value+dbms_random.value(1,1000),';
         else
             l_insert := l_insert || 'dbms_random.string(''A'',' ||x.data_length || '),';
         end if;
     end loop;
     l_insert := rtrim(l_insert,',') ||
                   ' from all_objects where rownum <= :n';

     loop
         execute immediate l_insert using p_records - l_rows;
         l_rows := l_rows + sql%rowcount;
         exit when ( l_rows >= p_records );
     end loop;
 end;
 /